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 the query execution command:

ydb sql --help

Parameters of the subcommand

Name

Description

-h, --help

Print general usage help.

-hh

Print complete usage help, including specific commands 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 Executing parameterized queries.

Examples

Note

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

Running DDL and DML together in a single query is not supported.

# Creating a table
ydb -p quickstart sql -s '
  CREATE TABLE series (
    series_id Uint64,
    title Utf8,
    series_info Utf8,
    release_date Date,
    PRIMARY KEY (series_id)
  );
'

# Populating with data and retrieving a sample
ydb -p quickstart sql -s '
    UPSERT INTO series (series_id, title, series_info, release_date) 
    VALUES (1, "Title1", "Info1", Cast("2023-04-20" as Date));
    SELECT * FROM series;
'

# Adding an index
ydb -p quickstart sql -s '
    ALTER TABLE series 
    ADD INDEX title_idx GLOBAL ON (title);
'

Command output:

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

To run a query from a file (for example, script1.yql) with JSON output

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

Command output:

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

Examples of passing parameters to scripts are given in the article on passing parameters to query execution commands.

Previous
Overview
Next
Parameterized query execution