Use SQLLine to work with Phoenix
The SQLLine section describes, how to connect to Phoenix via SQLLine. The main data operations available in Phoenix, while working with SQLLine, are listed below. They are based on the example described in the HBase data model section. We use this example to show how to work with data both in HBase and Phoenix in order to understand the similarities and differences between them.
The full list of these commands as well as other grammar specifications you can find in Phoenix documentation.
|
NOTE
Before executing these commands, we recommend you to read about HBase data model and basic data operations in HBase. |
Step 1. Create a table
Most syntax constructions in Phoenix correspond to SQL. To create a new table, use the CREATE TABLE keywords, then define a table name in the double quotes, and after it describe all the columns with their types in the round brackets. If you want to distribute columns between different column families in HBase, you should separate column family names from column qualifiers by dots ., as shown below. The default column family name is 0. After column descriptions, you can also set some table-scope parameters.
The following command creates the articles table with the primary key row_id, which will be stored in HBase as a row key. Besides, the command defines two columns inside of the column family basic (author, header) and four columns in the column family tags (arch, concepts, tutorials, ref). We also define that all columns will store five versions of each data value.
|
TIP
Use double quotes |
CREATE TABLE "articles" ("row_id" VARCHAR PRIMARY KEY, "basic"."author" VARCHAR, "basic"."header" VARCHAR, "tags"."arch" BOOLEAN, "tags"."concepts" BOOLEAN, "tags"."tutorials" BOOLEAN, "tags"."ref" BOOLEAN) VERSIONS=5;The output is similar to the following:
No rows affected (1.313 seconds)
Step 2. Get information about the table
To check existence of the table and to get its description, you can use one of the following SQLLine commands:
-
!tables — returns the list of all tables in Phoenix:
!tablesThe output:
+------------+--------------+-------------+---------------+----------+------------+----------------------------+-----------------+--------------+-----------------+---------------+---------------+-----------------+------------+-------------+----------------+----------------------+--------------------+-----------------------+ | TABLE_CAT | TABLE_SCHEM | TABLE_NAME | TABLE_TYPE | REMARKS | TYPE_NAME | SELF_REFERENCING_COL_NAME | REF_GENERATION | INDEX_STATE | IMMUTABLE_ROWS | SALT_BUCKETS | MULTI_TENANT | VIEW_STATEMENT | VIEW_TYPE | INDEX_TYPE | TRANSACTIONAL | IS_NAMESPACE_MAPPED | GUIDE_POSTS_WIDTH | TRANSACTION_PROVIDER | +------------+--------------+-------------+---------------+----------+------------+----------------------------+-----------------+--------------+-----------------+---------------+---------------+-----------------+------------+-------------+----------------+----------------------+--------------------+-----------------------+ | | SYSTEM | CATALOG | SYSTEM TABLE | | | | | | false | null | false | | | | false | false | null | | | | SYSTEM | FUNCTION | SYSTEM TABLE | | | | | | false | null | false | | | | false | false | null | | | | SYSTEM | LOG | SYSTEM TABLE | | | | | | true | 32 | false | | | | false | false | null | | | | SYSTEM | SEQUENCE | SYSTEM TABLE | | | | | | false | null | false | | | | false | false | null | | | | SYSTEM | STATS | SYSTEM TABLE | | | | | | false | null | false | | | | false | false | null | | | | | articles | TABLE | | | | | | false | null | false | | | | false | false | null | | +------------+--------------+-------------+---------------+----------+------------+----------------------------+-----------------+--------------+-----------------+---------------+---------------+-----------------+------------+-------------+----------------+----------------------+--------------------+-----------------------+
-
!describe — returns a description for the defined table containing all column names, data types, and other attributes:
!describe "articles"The output:
+------------+--------------+-------------+--------------+------------+------------+--------------+----------------+-----------------+-----------------+-----------+----------+-------------+----------+ | TABLE_CAT | TABLE_SCHEM | TABLE_NAME | COLUMN_NAME | DATA_TYPE | TYPE_NAME | COLUMN_SIZE | BUFFER_LENGTH | DECIMAL_DIGITS | NUM_PREC_RADIX | NULLABLE | REMARKS | COLUMN_DEF | SQL_DATA | +------------+--------------+-------------+--------------+------------+------------+--------------+----------------+-----------------+-----------------+-----------+----------+-------------+----------+ | | | articles | row_id | 12 | VARCHAR | null | null | null | null | 0 | | | null | | | | articles | author | 12 | VARCHAR | null | null | null | null | 1 | | | null | | | | articles | header | 12 | VARCHAR | null | null | null | null | 1 | | | null | | | | articles | arch | 16 | BOOLEAN | null | null | null | null | 1 | | | null | | | | articles | concepts | 16 | BOOLEAN | null | null | null | null | 1 | | | null | | | | articles | tutorials | 16 | BOOLEAN | null | null | null | null | 1 | | | null | | | | articles | ref | 16 | BOOLEAN | null | null | null | null | 1 | | | null | +------------+--------------+-------------+--------------+------------+------------+--------------+----------------+-----------------+-----------------+-----------+----------+-------------+----------+
If the command output does not fit in the console window, you can log out from SQLLine (using the !quit command) and run the following command to expand the output width:
$ stty cols 400Another way is to run the following command for line-by-line data output in SQLLine (to return back, use horizontal):
!outputformat verticalIn addition to using SQLLine, you can also check the created table in HBase shell using the exists and describe commands. Check that the table exists and all defined column families are created:
exists 'articles'The output:
Table articles does exist Took 0.0453 seconds => true
describe 'articles'The output:
Table articles is ENABLED
articles, {TABLE_ATTRIBUTES => {coprocessor$1 => '|org.apache.phoenix.coprocessor.ScanRegionObserver|805306366|', coprocessor$2 => '|org.apache.phoenix.coprocessor.UngroupedAggregateRegionObserver|805306366|', coprocessor$3 => '|org.apache.phoenix.coprocessor.GroupedAggregateRegionObserver|805306366|', coprocessor$4 => '|org.apache.phoenix.coprocessor.ServerCachingEndpointImpl|805306366|', coprocessor$5 => '|org.apache.phoenix.hbase.index.Indexer|805306366|index.builder=org.apache.phoenix.index.PhoenixIndexBuilder,org.apache.hadoop.hbase.index.codec.class=org.apache.phoenix.index.PhoenixIndexCodec'}
COLUMN FAMILIES DESCRIPTION
{NAME => 'basic', VERSIONS => '5', EVICT_BLOCKS_ON_CLOSE => 'false', NEW_VERSION_BEHAVIOR => 'false', KEEP_DELETED_CELLS => 'FALSE', CACHE_DATA_ON_WRITE => 'false', DATA_BLOCK_ENCODING => 'FAST_DIFF', TTL => 'FOREVER', MIN_VERSIONS => '0', REPLICATION_SCOPE => '0', BLOOMFILTER => 'NONE', CACHE_INDEX_ON_WRITE => 'false', IN_MEMORY => 'false', CACHE_BLOOMS_ON_WRITE => 'false', PREFETCH_BLOCKS_ON_OPEN => 'false', COMPRESSION => 'NONE', BLOCKCACHE => 'true', BLOCKSIZE => '65536'}
{NAME => 'tags', VERSIONS => '5', EVICT_BLOCKS_ON_CLOSE => 'false', NEW_VERSION_BEHAVIOR => 'false', KEEP_DELETED_CELLS => 'FALSE', CACHE_DATA_ON_WRITE => 'false', DATA_BLOCK_ENCODING => 'FAST_DIFF', TTL => 'FOREVER', MIN_VERSIONS => '0', REPLICATION_SCOPE => '0', BLOOMFILTER => 'NONE', CACHE_INDEX_ON_WRITE => 'false', IN_MEMORY => 'false', CACHE_BLOOMS_ON_WRITE => 'false', PREFETCH_BLOCKS_ON_OPEN => 'false', COMPRESSION => 'NONE', BLOCKCACHE => 'true', BLOCKSIZE => '65536'}
2 row(s)
Took 0.0356 seconds
Step 3. Insert new data into the table
To add new data into the created table, you should use the UPSERT command. It combines two SQL commands: INSERT and UPDATE. If there is a row with the specified primary key, the data is updated, otherwise it is inserted.
The following command adds new data for the row key article1. If column names are not defined after the table name (in the round brackets), the values are inserted in the order defined during the table creation. The last column ref is not defined, so it will not be stored in HBase.
|
TIP
To define |
UPSERT INTO "articles" VALUES('article1', 'Test author', 'Test article', true, true, true);The following command adds new data for the row key article2. Column names are explicitly specified after the table name; the missed columns will not be stored in HBase:
UPSERT INTO "articles"("row_id", "basic"."author", "basic"."header", "tags"."ref") VALUES('article2', 'Test author2', 'Test article2', true);The output of each command is similar to the following:
1 row affected (0.018 seconds)
Step 4. Select data from the table
To select all data from the created table, use the SELECT command:
SELECT * FROM "articles";The output:
+-----------+---------------+----------------+-------+-----------+------------+-------+ | row_id | author | header | arch | concepts | tutorials | ref | +-----------+---------------+----------------+-------+-----------+------------+-------+ | article1 | Test author | Test article | true | true | true | | | article2 | Test author2 | Test article2 | | | | true | +-----------+---------------+----------------+-------+-----------+------------+-------+ 2 rows selected (0.06 seconds)
You can use different SQL constructions for filtering, sorting, and grouping the output results: WHERE, GROUP BY, ORDER BY, and other. Subqueries are also allowed.
The following query selects the data with the specified primary key value:
SELECT * FROM "articles" WHERE "row_id" = 'article1';The result should look like this:
+-----------+--------------+---------------+-------+-----------+------------+------+ | row_id | author | header | arch | concepts | tutorials | ref | +-----------+--------------+---------------+-------+-----------+------------+------+ | article1 | Test author | Test article | true | true | true | | +-----------+--------------+---------------+-------+-----------+------------+------+ 1 row selected (0.038 seconds)
You can also check the table content in HBase shell by running the scan command:
scan 'articles'In the command output, you can see all the added values. In addition to the user-defined columns, Phoenix also adds one service column for each row key: in our example, with the encoded name \x00\x00\x00\x00 and the encoded value x. This column is used for performance improvements during scan operations — do not change it.
|
TIP
Notice that data added by Phoenix is encoded. That is why it is not recommended to use HBase API to work with such data as it requires decoding. Use Phoenix instead of it. |
ROW COLUMN+CELL article1 column=basic:\x00\x00\x00\x00, timestamp=1638285442663, value=x article1 column=basic:\x80\x0B, timestamp=1638285442663, value=Test author article1 column=basic:\x80\x0C, timestamp=1638285442663, value=Test article article1 column=tags:\x80\x0D, timestamp=1638285442663, value=\x01 article1 column=tags:\x80\x0E, timestamp=1638285442663, value=\x01 article1 column=tags:\x80\x0F, timestamp=1638285442663, value=\x01 article2 column=basic:\x00\x00\x00\x00, timestamp=1638285450913, value=x article2 column=basic:\x80\x0B, timestamp=1638285450913, value=Test author2 article2 column=basic:\x80\x0C, timestamp=1638285450913, value=Test article2 article2 column=tags:\x80\x10, timestamp=1638285450913, value=\x01 2 row(s) Took 0.0214 seconds
Step 5. Update data in the table
To update some data values in the table, use the same command as for inserting new data, that is, UPSERT. The following command updates the value of the header column in the row with the article1 key:
UPSERT INTO "articles"("row_id", "basic"."header") VALUES('article1', 'Test article. Version 2');The output:
1 row affected (0.015 seconds)
Now, if you apply the SELECT command to the table, you will see the updated data:
SELECT * FROM "articles";The output:
+-----------+---------------+--------------------------+-------+-----------+------------+-------+ | row_id | author | header | arch | concepts | tutorials | ref | +-----------+---------------+--------------------------+-------+-----------+------------+-------+ | article1 | Test author | Test article. Version 2 | true | true | true | | | article2 | Test author2 | Test article2 | | | | true | +-----------+---------------+--------------------------+-------+-----------+------------+-------+ 2 rows selected (0.041 seconds)
Check it also in HBase shell by executing the following command:
scan 'articles'Its result is similar:
ROW COLUMN+CELL article1 column=basic:\x00\x00\x00\x00, timestamp=1638285553403, value=x article1 column=basic:\x80\x0B, timestamp=1638285442663, value=Test author article1 column=basic:\x80\x0C, timestamp=1638285553403, value=Test article. Version 2 article1 column=tags:\x80\x0D, timestamp=1638285442663, value=\x01 article1 column=tags:\x80\x0E, timestamp=1638285442663, value=\x01 article1 column=tags:\x80\x0F, timestamp=1638285442663, value=\x01 article2 column=basic:\x00\x00\x00\x00, timestamp=1638285450913, value=x article2 column=basic:\x80\x0B, timestamp=1638285450913, value=Test author2 article2 column=basic:\x80\x0C, timestamp=1638285450913, value=Test article2 article2 column=tags:\x80\x10, timestamp=1638285450913, value=\x01 2 row(s) Took 0.0125 seconds
However, as we specified the need to store five versions of data values when creating the table, HBase stores not only the last version, but also the previous one. To see this, it is necessary to run in HBase shell the scan command with the TIMERANGE argument:
----ma
scan 'articles', {TIMERANGE => [1638285442663, 1638285450914]}
----The output represents value versions within the specified timestamp range:
ROW COLUMN+CELL article1 column=basic:\x00\x00\x00\x00, timestamp=1638285442663, value=x article1 column=basic:\x80\x0B, timestamp=1638285442663, value=Test author article1 column=basic:\x80\x0C, timestamp=1638285442663, value=Test article article1 column=tags:\x80\x0D, timestamp=1638285442663, value=\x01 article1 column=tags:\x80\x0E, timestamp=1638285442663, value=\x01 article1 column=tags:\x80\x0F, timestamp=1638285442663, value=\x01 article2 column=basic:\x00\x00\x00\x00, timestamp=1638285450913, value=x article2 column=basic:\x80\x0B, timestamp=1638285450913, value=Test author2 article2 column=basic:\x80\x0C, timestamp=1638285450913, value=Test article2 article2 column=tags:\x80\x10, timestamp=1638285450913, value=\x01 2 row(s) Took 0.0112 seconds
Step 6. Delete data from the table
To delete data values from the table, use the DELETE command. The following command deletes all the table rows, where the value of the header column is equal to Test article. Version 2.
DELETE FROM "articles" WHERE "basic"."header" = 'Test article. Version 2';The output:
1 row affected (0.018 seconds)
|
CAUTION
Notice that unlike HBase, when you run |
If you apply SELECT to the table now, you will see only one row:
SELECT * FROM "articles";The output shows that the first row has been deleted:
+-----------+---------------+----------------+-------+-----------+-------------+ | row_id | author | header | arch | concepts | tutorials | +-----------+---------------+----------------+-------+-----------+-------------+ | article2 | Test author2 | Test article2 | | | | +-----------+---------------+----------------+-------+-----------+-------------+ 1 row selected (0.042 seconds)
HBase shell returns a result similar to the previous one:
scan 'articles'The output:
ROW COLUMN+CELL article2 column=basic:\x00\x00\x00\x00, timestamp=1638283068620, value=x article2 column=basic:\x80\x0B, timestamp=1638283068620, value=Test author2 article2 column=basic:\x80\x0C, timestamp=1638283068620, value=Test article2 article2 column=tags:\x80\x10, timestamp=1638283068620, value=\x01 1 row(s) Took 0.0101 seconds
The following command deletes the row of the table with the specified primary key value:
DELETE FROM "articles" WHERE "row_id" = 'article2';After this operation, the table becomes empty:
SELECT * FROM "articles";The output:
+---------+---------+---------+-------+-----------+------------+------+ | row_id | author | header | arch | concepts | tutorials | ref | +---------+---------+---------+-------+-----------+------------+------+ +---------+---------+---------+-------+-----------+------------+------+ No rows selected (0.027 seconds)
Step 7. Alter the table
To change the table structure, use the ALTER TABLE command. The following command adds a new column family temp and the review column in it:
ALTER TABLE "articles" ADD "temp"."review" VARCHAR VERSIONS=3;The result is:
No rows affected (6.604 seconds)
Check the updated structure using the !describe command in SQLLine:
!describe "articles"The output contains the added column review:
+------------+--------------+-------------+--------------+------------+------------+--------------+----------------+-----------------+-----------------+-----------+----------+-------------+----------+ | TABLE_CAT | TABLE_SCHEM | TABLE_NAME | COLUMN_NAME | DATA_TYPE | TYPE_NAME | COLUMN_SIZE | BUFFER_LENGTH | DECIMAL_DIGITS | NUM_PREC_RADIX | NULLABLE | REMARKS | COLUMN_DEF | SQL_DATA | +------------+--------------+-------------+--------------+------------+------------+--------------+----------------+-----------------+-----------------+-----------+----------+-------------+----------+ | | | articles | row_id | 12 | VARCHAR | null | null | null | null | 0 | | | null | | | | articles | author | 12 | VARCHAR | null | null | null | null | 1 | | | null | | | | articles | header | 12 | VARCHAR | null | null | null | null | 1 | | | null | | | | articles | arch | 16 | BOOLEAN | null | null | null | null | 1 | | | null | | | | articles | concepts | 16 | BOOLEAN | null | null | null | null | 1 | | | null | | | | articles | tutorials | 16 | BOOLEAN | null | null | null | null | 1 | | | null | | | | articles | ref | 16 | BOOLEAN | null | null | null | null | 1 | | | null | | | | articles | review | 12 | VARCHAR | null | null | null | null | 1 | | | null | +------------+--------------+-------------+--------------+------------+------------+--------------+----------------+-----------------+-----------------+-----------+----------+-------------+----------+
In HBase shell, use the describe command to see the added column family:
describe 'articles'The output:
Table articles is ENABLED
articles, {TABLE_ATTRIBUTES => {coprocessor$1 => '|org.apache.phoenix.coprocessor.ScanRegionObserver|805306366|', coprocesso
r$2 => '|org.apache.phoenix.coprocessor.UngroupedAggregateRegionObserver|805306366|', coprocessor$3 => '|org.apache.phoenix.
coprocessor.GroupedAggregateRegionObserver|805306366|', coprocessor$4 => '|org.apache.phoenix.coprocessor.ServerCachingEndpo
intImpl|805306366|', coprocessor$5 => '|org.apache.phoenix.hbase.index.Indexer|805306366|index.builder=org.apache.phoenix.in
dex.PhoenixIndexBuilder,org.apache.hadoop.hbase.index.codec.class=org.apache.phoenix.index.PhoenixIndexCodec'}
COLUMN FAMILIES DESCRIPTION
{NAME => 'basic', VERSIONS => '5', EVICT_BLOCKS_ON_CLOSE => 'false', NEW_VERSION_BEHAVIOR => 'false', KEEP_DELETED_CELLS =>
'FALSE', CACHE_DATA_ON_WRITE => 'false', DATA_BLOCK_ENCODING => 'FAST_DIFF', TTL => 'FOREVER', MIN_VERSIONS => '0', REPLICAT
ION_SCOPE => '0', BLOOMFILTER => 'NONE', CACHE_INDEX_ON_WRITE => 'false', IN_MEMORY => 'false', CACHE_BLOOMS_ON_WRITE => 'fa
lse', PREFETCH_BLOCKS_ON_OPEN => 'false', COMPRESSION => 'NONE', BLOCKCACHE => 'true', BLOCKSIZE => '65536'}
{NAME => 'tags', VERSIONS => '5', EVICT_BLOCKS_ON_CLOSE => 'false', NEW_VERSION_BEHAVIOR => 'false', KEEP_DELETED_CELLS => '
FALSE', CACHE_DATA_ON_WRITE => 'false', DATA_BLOCK_ENCODING => 'FAST_DIFF', TTL => 'FOREVER', MIN_VERSIONS => '0', REPLICATI
ON_SCOPE => '0', BLOOMFILTER => 'NONE', CACHE_INDEX_ON_WRITE => 'false', IN_MEMORY => 'false', CACHE_BLOOMS_ON_WRITE => 'fal
se', PREFETCH_BLOCKS_ON_OPEN => 'false', COMPRESSION => 'NONE', BLOCKCACHE => 'true', BLOCKSIZE => '65536'}
{NAME => 'temp', VERSIONS => '3', EVICT_BLOCKS_ON_CLOSE => 'false', NEW_VERSION_BEHAVIOR => 'false', KEEP_DELETED_CELLS => '
FALSE', CACHE_DATA_ON_WRITE => 'false', DATA_BLOCK_ENCODING => 'FAST_DIFF', TTL => 'FOREVER', MIN_VERSIONS => '0', REPLICATI
ON_SCOPE => '0', BLOOMFILTER => 'NONE', CACHE_INDEX_ON_WRITE => 'false', IN_MEMORY => 'false', CACHE_BLOOMS_ON_WRITE => 'fal
se', PREFETCH_BLOCKS_ON_OPEN => 'false', COMPRESSION => 'NONE', BLOCKCACHE => 'true', BLOCKSIZE => '65536'}
3 row(s)
Took 0.0222 seconds
Step 8. Drop the table
To delete the table permanently, use the DROP TABLE command:
DROP TABLE "articles";The output:
No rows affected (1.246 seconds)
Now, if you run the !tables command, you will see that the articles table does not exist anymore:
+------------+--------------+-------------+---------------+----------+------------+----------------------------+-----------------+--------------+-----------------+---------------+---------------+-----------------+------------+-------------+----------------+----------------------+--------------------+-----------------------+ | TABLE_CAT | TABLE_SCHEM | TABLE_NAME | TABLE_TYPE | REMARKS | TYPE_NAME | SELF_REFERENCING_COL_NAME | REF_GENERATION | INDEX_STATE | IMMUTABLE_ROWS | SALT_BUCKETS | MULTI_TENANT | VIEW_STATEMENT | VIEW_TYPE | INDEX_TYPE | TRANSACTIONAL | IS_NAMESPACE_MAPPED | GUIDE_POSTS_WIDTH | TRANSACTION_PROVIDER | +------------+--------------+-------------+---------------+----------+------------+----------------------------+-----------------+--------------+-----------------+---------------+---------------+-----------------+------------+-------------+----------------+----------------------+--------------------+-----------------------+ | | SYSTEM | CATALOG | SYSTEM TABLE | | | | | | false | null | false | | | | false | false | null | | | | SYSTEM | FUNCTION | SYSTEM TABLE | | | | | | false | null | false | | | | false | false | null | | | | SYSTEM | LOG | SYSTEM TABLE | | | | | | true | 32 | false | | | | false | false | null | | | | SYSTEM | SEQUENCE | SYSTEM TABLE | | | | | | false | null | false | | | | false | false | null | | | | SYSTEM | STATS | SYSTEM TABLE | | | | | | false | null | false | | | | false | false | null | | +------------+--------------+-------------+---------------+----------+------------+----------------------------+-----------------+--------------+-----------------+---------------+---------------+-----------------+------------+-------------+----------------+----------------------+--------------------+-----------------------+