WITH

Specified after the data source in FROM and used to provide additional hints for table usage. Hints cannot be specified for subqueries and named expressions.

The following values are supported:

• INFER_SCHEMA — sets the flag for inferring the table schema. The behavior is similar to setting the yt.InferSchema pragma, but only for a specific data source. You can specify the number of rows to infer (a number from 1 to 1000).
• FORCE_INFER_SCHEMA — sets the flag for forcing schema inference of the table. The behavior is similar to setting the yt.ForceInferSchema pragma, but only for a specific data source. You can specify the number of rows to infer (a number from 1 to 1000).
• DIRECT_READ — suppresses the operation of some optimizers and forces the table contents to be used as is. The behavior is similar to setting the debug DirectRead pragma, but only for a specific data source.
• INLINE — indicates that the table contents are small and should be processed using an in-memory representation. The actual table size is not checked, and if it is large, the query may fail due to memory exhaustion.
• UNORDERED — suppresses the use of the table's original sorting.
• XLOCK — indicates that an exclusive lock should be taken on the table. Useful when the table is read during the query metaprogram processing stage and then its contents are updated in the main query. It helps avoid data loss if an external process modifies the table between the execution of the metaprogram phase and the main part of the query.
• SCHEMA type — indicates that the specified table schema should be used entirely, ignoring the schema in the metadata.
• COLUMNS type — indicates that the specified types should be used for columns whose names match the column names in the table metadata, as well as which additional columns are present in the table.
• IGNORETYPEV3, IGNORE_TYPE_V3 — sets the flag to ignore type_v3 types in the table. The behavior is similar to setting the yt.IgnoreTypeV3 pragma, but only for a specific data source.

When working with external file data sources, you can additionally specify a number of parameters:

• FORMAT: data storage format in file storages in federated queries. Allowed values: csv_with_names, tsv_with_names, json_list, json_each_row, json_as_string, parquet, raw.
• COMPRESSION: file compression format in file storages in federated queries. Valid values: gzip, zstd, lz4, brotli, bzip2, xz.
• PARTITIONED_BY - a list of partitioning columns of data in file storages in federated queries. Contains a list of columns in the order they are placed in the file storage.
• projection.enabled - a flag for enabling extended data partitioning. Valid values: true, false.
• projection.<field_name>.type - field type of extended data partitioning. Valid values: integer, enum, date.
• projection.<field_name>.<options> - extended properties of the field of extended data partitioning.

When reading from a topic in streaming queries, you can specify watermarks parameters:

• WATERMARK — expression for calculating the watermark. Currently, only the write time to a topic with a constant delay is supported. Format: __ydb_write_time - Interval("<delay>"), where <delay> is specified in ISO 8601 format.
• WATERMARK_GRANULARITY — watermark generation frequency. The smaller it is, the higher the CPU consumption by the query and the lower the response latency. Only relevant for streaming queries. Specified in ISO 8601 format. Default value — 1 second.
• WATERMARK_IDLE_TIMEOUT — period after which an idle partition will be excluded from the combined watermark calculation. Only relevant for streaming queries. Specified in ISO 8601 format. Default value — 5 seconds.

When specifying hints SCHEMA and COLUMNS, the type value must be a structure type.

Examples

SELECT key FROM my_table WITH INFER_SCHEMA;
SELECT key FROM my_table WITH FORCE_INFER_SCHEMA="42";

$s = (SELECT COUNT(*) FROM my_table WITH XLOCK);

INSERT INTO my_table WITH TRUNCATE
SELECT EvaluateExpr($s) AS a;

SELECT key, value FROM my_table WITH SCHEMA Struct<key:String, value:Int32>;

SELECT key, value FROM my_table WITH COLUMNS Struct<value:Int32?>;

SELECT key, value FROM EACH($my_tables) WITH SCHEMA Struct<key:String, value:List<Int32>>;

SELECT
    *
FROM
    my_topic
WITH (
    FORMAT = json_each_row,
    SCHEMA = (
        ts String
    ),
    WATERMARK = __ydb_write_time - Interval("PT5S"),
    WATERMARK_GRANULARITY = "PT1S",
    WATERMARK_IDLE_TIMEOUT = "PT5S"
);