Integration in seconds with YDB CLI

Heading for solutions

Developed initially as a thin wrapper around YDB API, YDB Command Line Interface (CLI) utility has recently extended its integration capabilities with the Linux ecosystem and acquired useful functions to solve a wide range of integration tasks out-of-the-box.

In this article, I will uncover some new features, which can dramatically improve your experience with YDB operations.

Connection profiles

I encourage you to use profiles, which encapsulate all the connection/authentication parameters under a short meaningful name, saved in a local configuration file. Your interaction with YDB CLI will go much faster and with fewer errors, as soon as you focus on a command you need to execute, rather than on a mess of connection attributes.

Stop doing like that:

ydb -e grpcs://ydb.mydomain.myorganization.net:2135 \
    -d /root/b1g8skpblkos03malf3s/etn01q5ko6sh271beftr \
    --ca-file ~/certs/ydb/root1.crt \
    --sa-key-file ~/secrets/mydb1_sa.json \
    --iam-endpoint iam1.iam.myorgqnization.net:443 \
    scheme ls

Start doing like this:

ydb -p myprofile scheme ls

Check this example in the YDB CLI documentation for the fastest way to convert.

Examples in this article use a quickstart connection profile. You may initialise it like this once to enjoy a simple copy-paste replay on your test database for the rest of this article (replace example endpoints and paths with your own):

ydb config profile create quickstart \
    -e grpcs://ydb.mydomain.myorganization.net:2135 \
    -d /root/b1g8skpblkos03malf3s/etn01q5ko6sh271beftr \
    --ca-file ~/certs/ydb/root1.crt \
    --sa-key-file ~/secrets/mydb1_sa.json \
    --iam-endpoint iam1.iam.myorgqnization.net:443

Machine-readable output

When you get data from YDB, you may need to do some automated processing with it, rather than just watch it on the screen. Historically and by default, most data retrieval functions of YDB CLI provide output in a so-called ‘pretty’ format which looks nice, but is extremely hard to parse, does not allow streaming, and is just inapplicable with long record sets.

Happily, there are options to solve all of the above-mentioned issues.

YQL query execution

To execute a YQL query without a limit of 1000 records per result set, streaming its output for further processing, use the table query execute command with the following options:

• -t scan to execute a single read-only statement with no limits for the raw count in the result set
• --format json-unicode to provide a newline-delimited JSON stream at the output. There are no newline characters inside the records, every JSON is a one-liner.

Consider the following example:

ydb -p quickstart table query execute \
  -q 'select id, col1 from my_table' \
  -t scan --format json-unicode

It produces output like that:

{"id":15,"col1":"Multiline record 1 line1\nLine2"}
{"id":17,"col1":"Record2"}
...

You can also get CSV and TSV representations, formatted with the same guarantees.

Topic read

Messages in topics are binary, so you can leverage its automatic conversion to Base64 to provide an output stream with guaranteed newline delimiters. In the example below, I also ask YDB CLI to wait for new messages coming in the topic and transfer them to the output upon arrival, until Ctrl+C is pressed:

ydb -p quickstart topic read mytopic1
  -c consumer1
  --wait
  --format newline-delimited
  --transform base64

The command’s output will look like that (assuming someone is publishing some messages to the topic):

SGVsbG8gd29ybGQK
U2NhbGUgaXQgZWFzeQo=
...

You may omit transforming output to Base64, once you’re sure that messages do not contain newline characters. For instance, it is true when you publish YQL result sets formatted as JSON to the topic.

Read more about formatting options for `topic read` in the YDB documentation.

Pipe in

Shame on us, but initially most of the YDB CLI commands could not take data for processing from stdin and process it efficiently on-the-fly. Now we fixed it, opening a whole world of Linux shell pipe integrations, and saving your time.

Import data

You can upload gigabytes of data using the import file CLI command taking advantage of the BulkUpsert YDB API and parallel execution:

ydb -p quickstart yql -s 'create table hackernews (
    id Int64, deleted Int32, type Utf8, by Utf8, time Datetime, text Utf8,
    dead Int32, parent Int64, poll Int32, kids Utf8, url Utf8, score Int32,
    title Utf8, parts Utf8, descendants Int32, primary key (id)'
curl https://datasets-documentation.s3.eu-west-3.amazonaws.com/hackernews/hacknernews.csv.gz | \
  gzip -dkc | \
  ydb -p quickstart import file csv -p hackernews --header

Besides CSV, ydb import file command supports JSON newline-delimited stream, Parquet, and TSV formats.

Prepartitioning and slicing

To maximize import speed, you can create a table with a predefined set of partitions to accommodate all data with no partition splits.

ydb -p quickstart yql -s 'CREATE TABLE table1 (
  ID UInt32,
  Data String,
  PRIMARY KEY (ID)
)
WITH ( AUTO_PARTITIONING_MIN_PARTITIONS_COUNT = 10,
       PARTITION_AT_KEYS = ( 1000000, 2000000, 3000000, 4000000, 5000000,
                             6000000, 7000000, 8000000, 9000000 )
)'

In the example above, I created a table with 10 predefined partitions at the specified key ranges. Setting min partitions count is necessary to avoid the automatic merge of partitions right after creation.

Now you need to provide a maximum parallel load to all the partitions. You’re lucky if your dataset has randomly distributed keys, but the more common situation is that this dataset was exported from some system, and keys are more or less sorted. To deal with that, there are two options.

Option 1: Load from multiple files

If your dataset is stored in a number of data files, give all their names to the YDB CLI in a single call:

ydb -p quickstart import file csv -p table1 -i part*.csv -i recent.csv

Option 2: Let YDB CLI slice one big file internally

If you have a single big file, give YDB CLI a hint that it can look for any newline character as the records separator in it. It will logically slice your file, processing it in parallel from different positions:

ydb -p quickstart import file csv -p table1 -i large_input_file.csv \
  --newline-delimited

Provided that there are enough resources on the database side, data can be imported at the speed close to your network interface bandwidth then.

Manage CLI profiles

You can link profile retrieval and modification functions, copying profiles with alterations:

ydb config profile get db1 | ydb config profile create db2 --user user1

YQL execution

This feature deserves a top-level section in this post. Read ahead!

Streaming YQL execution

In CLI version 2.2.0, all YQL execution commands ( table query execute, yql, scripting yql) received an upgrade enabling them not just to get JSON parameter values from stdin or files, but also to accept a stream of parameter values for multiple YQL execution, making a real YQL data processor out of the CLI.

Iterative execution

In its simplest form, you may just define a separator between different parameter sets on the input stream, using the --stdin-format newline-delimited option.

Suppose we have a text file file1 which contains three one-liner JSON documents delimited with a newline character:

{"a":10,"b":20}
{"a":15,"b":25}
{"a":20,"b":30}

Running the following example, the YQL request will be executed three times:

cat file1 | ydb -p quickstart table query execute \
  -q 'declare $a as Int64;
      declare $b as Int64;
      select $a * $b as result' \
  --stdin-format newline-delimited \
  --format json-unicode

Providing consequent three JSON result sets to the output:

{"result":200}
{"result":375}
{"result":600}

Compared to a separate execution in multiple CLI calls, we gain performance by connecting only once to the database and leveraging the requests cache.

New requests are submitted for execution every time a newline character is coming at the input. So, you may pipe from a source with an unpredictable rate of producing new data, like reading from a topic.

Let’s take a look at a complete example with data generation, publishing it to a topic, and writing from a topic to a database table.

First, create database objects:

ydb -p quickstart yql -s \
  'create table test_table_2 ( id Int64, primary key(id) )'
ydb -p quickstart topic create test_topic_2
ydb -p quickstart topic consumer add test_topic_2 --consumer c1

Next, run a generator in a separate terminal window, which publishes a new message to the topic every second:

for i in $(seq 1 1000);do echo "{\"id\":$i}";sleep 1;done | \
ydb -p quickstart topic write test_topic_2 --format newline-delimited

Finally, run a processor which consumes the topic, writes data to the database, and provides feedback to the output:

ydb -p quickstart topic read test_topic_2 -w -c c1 \
  --format newline-delimited | \
ydb -p quickstart table query execute \
  -q 'declare $id as Int64;
      upsert into test_table_2 (id) values ( $id );
      select "ID written to the table: " || CAST($id as Utf8)' \
  --stdin-format newline-delimited \
  --format csv

Batch execution

As you may have noticed, running a separate YQL query for each small parameter set may be unreasonably costly. What if we could batch it to improve performance?

YDB CLI supports such a scenario, triggered with a --batch option:

• iterative (default): no batching
• adaptive: CLI will cut batches of parameter sets from the input stream, considering the maximum batch size and maximum processing delay

When using batches, you need to declare a parameter with a List<> or List<Struct<>> type in the YQL query, and tell CLI its name in a separate option --stdin-par.

In the example below we use batching capability to perform multiple deletes from a table by the primary key.

First, let’s create a database table and fill it in with a number of records:

ydb -p quickstart yql \
  -s 'create table test_delete_1( id UInt64 not null, primary key (id))'
for i in $(seq 1 100000);do echo "$i";done | \
  ydb -p quickstart import file csv -p test_delete_1

Now delete all records with IDs greater than 10:

ydb -p quickstart table query execute -t scan \
  -q 'select t.id from test_delete_1 as t where t.id > 10' \
  --format json-unicode | \
ydb -p quickstart table query execute \
  -q 'declare $lines as List<Struct<id:UInt64>>;
      delete from test_delete_1 where id in 
        (select tl.id from AS_TABLE($lines) as tl)' \
  --stdin-format newline-delimited \
  --stdin-par lines \
  --batch adaptive \
  --batch-limit 10000

The ones who are curious can try to rewrite the example above without batching and see the difference in the execution time.

See a complete guide to the query parametrization options in the YDB CLI documentation.

Conclusion

In this post, I focused on the particular useful integration cases and relevant options supported by YDB CLI. There are plenty of other ways to use the new features, and many additional options to learn in the YDB documentation.

You’re welcome to suggest your own cases in the comments under this post.

And, we do not stop. Subscribe and follow!