Query execution

You can use the ydb sql subcommand to execute an SQL query. The query can be of any type (DDL, DML, etc.) and can consist of several subqueries. The ydb sql subcommand establishes a streaming connection and retrieves data through it. With in-stream query execution, no limit is imposed on the amount of data read. Data can also be written using this command, which is more efficient when executing repeated queries with data passed through parameters.

General format of the command:

ydb [global options...] sql [options...]

View the description of this command by calling it with --help option:

ydb sql --help

Parameters of the subcommand

Name

Description

-h, --help

Print general usage help.

-hh

Print complete usage help, including specific options not shown with --help.

-s, --script

Script (query) text to execute.

-f, --file

Path to a file with query text to execute. Path - means reading query text from stdin which disables passing parameters via stdin.

--stats

Statistics mode.
Available options:

  • none (default): Do not collect statistics.
  • basic: Collect aggregated statistics for updates and deletes per table.
  • full: Include execution statistics and plan in addition to basic.
  • profile: Collect detailed execution statistics, including statistics for individual tasks and channels.

--explain

Execute an explain request for the query. Displays the query's logical plan. The query is not actually executed and does not affect database data.

--explain-ast

Same as --explain, but in addition to the query's logical plan, an abstract syntax tree (AST) is printed. The AST section contains a representation in the internal miniKQL language.

--explain-analyze

Execute the query in EXPLAIN ANALYZE mode. Displays the query execution plan. Query results are ignored.
Important note: The query is actually executed, so any changes will be applied to the database.

--format

Output format.
Available options:

  • pretty (default): Human-readable format.
  • json-unicode: JSON output with binary strings Unicode-encoded and each JSON string in a separate line.
  • json-unicode-array: JSON output with binary strings Unicode-encoded and the result output as an array of JSON strings with each JSON string in a separate line.
  • json-base64: JSON output with binary strings Base64-encoded and each JSON string in a separate line.
  • json-base64-array: JSON output with binary strings Base64-encoded and the result output as an array of JSON strings with each JSON string in a separate line;
  • parquet: Output in Apache Parquet format.
  • csv: Output in CSV format.
  • tsv: Output in TSV format.

Working with parameterized queries

For a detailed description with examples on how to use parameterized queries, see Running parameterized queries.

Examples

Note

The examples use the quickstart profile. To learn more, see Creating a profile to connect to a test database.

A script to create a table, populate it with data, and select data from the table:

ydb -p quickstart sql -s '
    CREATE TABLE series (series_id Uint64, title Utf8, series_info Utf8, release_date Date, PRIMARY KEY (series_id));
    COMMIT;
    UPSERT INTO series (series_id, title, series_info, release_date) values (1, "Title1", "Info1", Cast("2023-04-20" as Date));
    COMMIT;
    SELECT * from series;
  '

Command output:

┌──────────────┬───────────┬─────────────┬──────────┐
| release_date | series_id | series_info | title    |
├──────────────┼───────────┼─────────────┼──────────┤
| "2023-04-20" | 1         | "Info1"     | "Title1" |
└──────────────┴───────────┴─────────────┴──────────┘

Running a script from the example above saved as the script1.yql file, with results output in JSON format:

ydb -p quickstart sql -f script1.yql --format json

Command output:

{"release_date":"2023-04-20","series_id":1,"series_info":"Info1","title":"Title1"}

You can find examples of passing parameters to queries in the article on how to pass parameters to ydb sql.

Previous
Overview
Next
Parameterized query execution