--- metadata: - name: generator content: Diplodoc Platform v5.50.6 alternate: - https://ydb.tech/docs/en/reference/ydb-cli/workload-tpch.md?version=main - https://ydb.tech/docs/ru/reference/ydb-cli/workload-tpch.md?version=main sourcePath: en/core/reference/ydb-cli/workload-tpch.md --- > **Documentation Index:** Fetch the complete configuration index at https://ydb.tech/docs/en/llms.txt # TPC-H workload The workload is based on the TPC-H [specification](https://www.tpc.org/tpc_documents_current_versions/pdf/tpc-h_v2.17.1.pdf), with the queries and table schemas adapted for YDB. The benchmark generates a workload typical for decision support systems. ## Common command options All commands support the common `--path` option, which specifies the path to the directory containing tables in the database: ```bash ydb workload tpch --path tpch/s1 ... ``` ### Available options {#common_options} | Name | Description | Default value | |------------------|------------------------------------|---------------| | `--path` or `-p` | Path to the directory with tables. | `/` | ## Initializing a load test { #init } Before running the benchmark, create a table: ```bash ydb workload tpch --path tpch/s1 init ``` See the command description: ```bash ydb workload tpch init --help ``` ### Available parameters {#init_options} | Name | Description | Default value | |-------------------------------------|----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|----------------------| | `--store ` | Table storage type. Possible values: `row`, `column`, `external-s3`. | `column` | | `--external-s3-prefix ` | Relevant only for external tables. Root path to the dataset in S3 storage. | | | `--external-s3-endpoint ` or `-e ` | Relevant only for external tables. Link to the S3 bucket with data. | | | `--string` | Use the `String` type for text fields. | `Utf8` | | `--datetime` | Use for time-related fields of type `Date`, `Datetime`, and `Timestamp`. | `Date32`, `Datetime64`, `Timestamp64` | | `--partition-size` | Maximum partition size in megabytes (AUTO_PARTITIONING_PARTITION_SIZE_MB) for row tables. | 2000 | | `--float-mode ` | Specifies the data type to use for fractional fields. Possible values are `double` and `decimal`. `double` uses the `Double` type, `decimal` uses `Decimal` with dimensions specified by the test standard. | `double` | | `--scale` | Sets the percentage of the benchmark's data size and workload to use, relative to full scale. | 1 | | `--clear` | If the table at the specified path already exists, it will be deleted. | | ## Loading data into a table { #load } The data will be generated and loaded into a table directly by ydb: ```bash ydb workload tpch --path tpch/s1 import generator --scale 1 ``` See the command description: ```bash ydb workload tpch import --help ``` ### Available options { #load_files_options } | Name | Description | Default value | |---------------------------------------------|--------------------------------------------------------------------------------------------------------------------------------------------------------------------|---------------| | `--scale ` | Data scale. Powers of ten are usually used. Also supports fractional scale, which is not described in the TPC-H specification. It can be useful for quickly testing small YDB databases. Examples: `0.1`, `0.3`. | | | `--tables ` | Comma-separated list of tables to generate. Available tables: `customer`, `nation`, `order_line`, `part_psupp`, `region`, `supplier`. | All tables | | `--proccess-count ` or `-C ` | Data generation can be split into several processes, this parameter specifies the number of processes. | 1 | | `--proccess-index ` or `-i ` | Data generation can be split into several processes, this parameter specifies the process number. | 0 | | `--state ` | Path to the generation state file. If the generation was interrupted for some reason, the download will be continued from the same place when it is started again. | | | `--clear-state` | Relevant if the `--state` parameter is specified. Clear the state file and start the download from the beginning. | | | `--dry-run` | Do not execute loading queries, but only display their text. | | ### Common parameters of the import command {#load_options} | Name | Description | Default value | |---|---|---| | `--upload-threads ` or `-t ` | The number of execution threads for data preparation. | The number of available cores on the client. | | `--bulk-size ` | The size of the chunk for sending data, in rows. | 10000 | | `--max-in-flight ` | The maximum number of data chunks that can be processed simultaneously. | 128 | | `--file-output-path ` or `-f ` | If this option is set, the data will not be loaded into the database, but will be saved to the directory . | | ## Run the load test { #run } Run the load: ```bash ydb workload tpch --path tpch/s1 run ``` During the test, load statistics are displayed for each request. See the command description: ```bash ydb workload tpch run --help ``` ### Common parameters for all load types {#run_options} | Name | Description | Default value | |---|---|---| | `--dry-run` | Do not execute initialization queries, but only display their text. | | | `--check-canonical` or `-c` | Use special version of queries (they have deterministic answers) and compare results with canonical ones. | | | `--output ` | The name of the file where the query execution results will be saved. | `results.out` | | `--iterations ` | The number of times each load query will be executed. | `1` | | `--json ` | The name of the file where query execution statistics will be saved in `json` format. | Not saved by default | | `--ministat ` | The name of the file where query execution statistics will be saved in `ministat` format. | Not saved by default | | `--csv ` | The name of the file to save the CSV version of the result table. | Not saved by default | | `--plan ` | The name of the file to save the query plan. Files like `..explain` and `..` will be saved in formats: `ast`, `json`, `svg`, and `table`. | Not saved by default | | `--query-prefix ` | Query prefix. Every prefix is a line that will be added to the beginning of each query. For multiple prefix lines use this option several times. | Not specified by default | | `--retries` | Max retry count for every request. | `0` | | `--include` | Names, numbers or ranges of query numbers to be executed as part of the load. Specified as a comma-separated list, e.g.: `1,2,4-6`. | All queries executed | | `--exclude` | Names, numbers or ranges of query numbers to be excluded from the load. Specified as a comma-separated list, e.g.: `1,2,4-6`. | None excluded by default | | `--verbose` or `-v` | Print additional information to the screen during query execution. | | | `--global-timeout ` | Global timeout for all queries. Supports time units (e.g., '5s', '1m'). Plain number interpreted as milliseconds. | Not specified by default. The time is unlimited. | | `--request-timeout ` | Timeout for each iteration of each query. Supports time units (e.g., '5s', '1m'). Plain number interpreted as milliseconds. | Not specified by default. The time is unlimited. | | `--threads ` or `-t ` | The number of parallel threads generating the load. Zero means that queries will be executed in the main thread; otherwise, queries will be mixed. | `0` | | `--stats ` | Extended execution statistics collection mode. Available values: `full`, `profile`. | `full` | ### TPC-H-specific options { #run_tpch_options } | Name | Description | Default value | |----------------------------|-----------------------------------------------------------------------------------------------------|---------------| | `--syntax ` | Syntax of the queries to use. Available values: `yql`. For more information about working with YQL syntax, see [here](../../yql/reference/index.md). | `yql` | | `--float-mode ` | Float mode. Can be `float`, `decimal` or `decimal_ydb`. If the value is `float` - float will be used, `decimal` means that decimal with canonical size specified in the TPC-H specification (`Decimal(12, 2)`) will be used, and `decimal_ydb` means that all float will be converted to `Decimal(22, 9)`. For more information about the Decimal type, see [documentation](../../yql/reference/types/primitive.md#numeric). | `float` | | `--scale ` | Scale factor. See the TPC-H specification, chapter 3. Used in TPC-H queries. Also supports fractional scale, which is not described in the TPC-H specification. It can be useful for quickly testing small YDB databases. Examples: `0.1`, `0.3`. For scale factors `1`, `10`, `100`, `1000` canonical answers are specified (see the `--check-canonical` option description). | 1 | ## Test data cleaning { #cleanup } Run cleaning: ```bash ydb workload tpch --path tpch/s1 clean ``` The command has no parameters.