This is an automated email from the ASF dual-hosted git repository. bridgetb pushed a commit to branch gh-pages in repository https://gitbox.apache.org/repos/asf/drill.git
commit b0836ef4565ad83e352c88c6d42847c0d15c9bee Author: Volodymyr Vysotskyi <[email protected]> AuthorDate: Tue Jan 28 19:58:24 2020 +0200 Changes after code review --- _data/docs.json | 250 +++++++++++++- _docs/performance-tuning/005-drill-metastore.md | 5 + .../drill-metastore/010-using-drill-metastore.md | 378 +++++++++++++++++++++ .../drill-metastore/030-drill-iceberg-metastore.md | 63 ++++ .../060-querying-the-information-schema.md | 9 +- .../sql-commands/005-supported-sql-commands.md | 46 +-- .../007-analyze-table-refresh-metadata.md | 160 ++++----- 7 files changed, 770 insertions(+), 141 deletions(-) diff --git a/_data/docs.json b/_data/docs.json index f74ef35..b62691b 100644 --- a/_data/docs.json +++ b/_data/docs.json @@ -3171,8 +3171,8 @@ } ], "children": [], - "next_title": "Identifying Performance Issues", - "next_url": "/docs/identifying-performance-issues/", + "next_title": "Drill Metastore", + "next_url": "/docs/drill-metastore/", "parent": "Query Plans and Tuning", "previous_title": "Throttling", "previous_url": "/docs/throttling/", @@ -4560,6 +4560,27 @@ "title": "Drill Explorer Introduction", "url": "/docs/drill-explorer-introduction/" }, + "Drill Iceberg Metastore": { + "breadcrumbs": [ + { + "title": "Drill Metastore", + "url": "/docs/drill-metastore/" + }, + { + "title": "Performance Tuning", + "url": "/docs/performance-tuning/" + } + ], + "children": [], + "next_title": "Identifying Performance Issues", + "next_url": "/docs/identifying-performance-issues/", + "parent": "Drill Metastore", + "previous_title": "Using Drill Metastore", + "previous_url": "/docs/using-drill-metastore/", + "relative_path": "_docs/performance-tuning/drill-metastore/020-drill-iceberg-metastore.md", + "title": "Drill Iceberg Metastore", + "url": "/docs/drill-iceberg-metastore/" + }, "Drill Introduction": { "breadcrumbs": [ { @@ -4577,6 +4598,66 @@ "title": "Drill Introduction", "url": "/docs/drill-introduction/" }, + "Drill Metastore": { + "breadcrumbs": [ + { + "title": "Performance Tuning", + "url": "/docs/performance-tuning/" + } + ], + "children": [ + { + "breadcrumbs": [ + { + "title": "Drill Metastore", + "url": "/docs/drill-metastore/" + }, + { + "title": "Performance Tuning", + "url": "/docs/performance-tuning/" + } + ], + "children": [], + "next_title": "Drill Iceberg Metastore", + "next_url": "/docs/drill-iceberg-metastore/", + "parent": "Drill Metastore", + "previous_title": "Drill Metastore", + "previous_url": "/docs/drill-metastore/", + "relative_path": "_docs/performance-tuning/drill-metastore/010-using-drill-metastore.md", + "title": "Using Drill Metastore", + "url": "/docs/using-drill-metastore/" + }, + { + "breadcrumbs": [ + { + "title": "Drill Metastore", + "url": "/docs/drill-metastore/" + }, + { + "title": "Performance Tuning", + "url": "/docs/performance-tuning/" + } + ], + "children": [], + "next_title": "Identifying Performance Issues", + "next_url": "/docs/identifying-performance-issues/", + "parent": "Drill Metastore", + "previous_title": "Using Drill Metastore", + "previous_url": "/docs/using-drill-metastore/", + "relative_path": "_docs/performance-tuning/drill-metastore/020-drill-iceberg-metastore.md", + "title": "Drill Iceberg Metastore", + "url": "/docs/drill-iceberg-metastore/" + } + ], + "next_title": "Using Drill Metastore", + "next_url": "/docs/using-drill-metastore/", + "parent": "Performance Tuning", + "previous_title": "Controlling Parallelization to Balance Performance with Multi-Tenancy", + "previous_url": "/docs/controlling-parallelization-to-balance-performance-with-multi-tenancy/", + "relative_path": "_docs/performance-tuning/050-drill-metastore.md", + "title": "Drill Metastore", + "url": "/docs/drill-metastore/" + }, "Drill Plan Syntax": { "breadcrumbs": [ { @@ -5478,8 +5559,8 @@ "next_title": "Query Plans", "next_url": "/docs/query-plans/", "parent": "Performance Tuning", - "previous_title": "Controlling Parallelization to Balance Performance with Multi-Tenancy", - "previous_url": "/docs/controlling-parallelization-to-balance-performance-with-multi-tenancy/", + "previous_title": "Drill Iceberg Metastore", + "previous_url": "/docs/drill-iceberg-metastore/", "relative_path": "_docs/performance-tuning/050-identifying-performance-issues.md", "title": "Identifying Performance Issues", "url": "/docs/identifying-performance-issues/" @@ -8426,8 +8507,8 @@ } ], "children": [], - "next_title": "Identifying Performance Issues", - "next_url": "/docs/identifying-performance-issues/", + "next_title": "Drill Metastore", + "next_url": "/docs/drill-metastore/", "parent": "Query Plans and Tuning", "previous_title": "Throttling", "previous_url": "/docs/throttling/", @@ -8456,6 +8537,66 @@ { "breadcrumbs": [ { + "title": "Drill Metastore", + "url": "/docs/drill-metastore/" + }, + { + "title": "Performance Tuning", + "url": "/docs/performance-tuning/" + } + ], + "children": [], + "next_title": "Drill Iceberg Metastore", + "next_url": "/docs/drill-iceberg-metastore/", + "parent": "Drill Metastore", + "previous_title": "Drill Metastore", + "previous_url": "/docs/drill-metastore/", + "relative_path": "_docs/performance-tuning/drill-metastore/010-using-drill-metastore.md", + "title": "Using Drill Metastore", + "url": "/docs/using-drill-metastore/" + }, + { + "breadcrumbs": [ + { + "title": "Drill Metastore", + "url": "/docs/drill-metastore/" + }, + { + "title": "Performance Tuning", + "url": "/docs/performance-tuning/" + } + ], + "children": [], + "next_title": "Identifying Performance Issues", + "next_url": "/docs/identifying-performance-issues/", + "parent": "Drill Metastore", + "previous_title": "Using Drill Metastore", + "previous_url": "/docs/using-drill-metastore/", + "relative_path": "_docs/performance-tuning/drill-metastore/020-drill-iceberg-metastore.md", + "title": "Drill Iceberg Metastore", + "url": "/docs/drill-iceberg-metastore/" + } + ], + "next_title": "Using Drill Metastore", + "next_url": "/docs/using-drill-metastore/", + "parent": "Performance Tuning", + "previous_title": "Controlling Parallelization to Balance Performance with Multi-Tenancy", + "previous_url": "/docs/controlling-parallelization-to-balance-performance-with-multi-tenancy/", + "relative_path": "_docs/performance-tuning/050-drill-metastore.md", + "title": "Drill Metastore", + "url": "/docs/drill-metastore/" + }, + { + "breadcrumbs": [ + { + "title": "Performance Tuning", + "url": "/docs/performance-tuning/" + } + ], + "children": [ + { + "breadcrumbs": [ + { "title": "Identifying Performance Issues", "url": "/docs/identifying-performance-issues/" }, @@ -8499,8 +8640,8 @@ "next_title": "Query Plans", "next_url": "/docs/query-plans/", "parent": "Performance Tuning", - "previous_title": "Controlling Parallelization to Balance Performance with Multi-Tenancy", - "previous_url": "/docs/controlling-parallelization-to-balance-performance-with-multi-tenancy/", + "previous_title": "Drill Iceberg Metastore", + "previous_url": "/docs/drill-iceberg-metastore/", "relative_path": "_docs/performance-tuning/050-identifying-performance-issues.md", "title": "Identifying Performance Issues", "url": "/docs/identifying-performance-issues/" @@ -9652,8 +9793,8 @@ } ], "children": [], - "next_title": "Identifying Performance Issues", - "next_url": "/docs/identifying-performance-issues/", + "next_title": "Drill Metastore", + "next_url": "/docs/drill-metastore/", "parent": "Query Plans and Tuning", "previous_title": "Throttling", "previous_url": "/docs/throttling/", @@ -15682,6 +15823,27 @@ "title": "Using Drill Explorer", "url": "/docs/using-drill-explorer/" }, + "Using Drill Metastore": { + "breadcrumbs": [ + { + "title": "Drill Metastore", + "url": "/docs/drill-metastore/" + }, + { + "title": "Performance Tuning", + "url": "/docs/performance-tuning/" + } + ], + "children": [], + "next_title": "Drill Iceberg Metastore", + "next_url": "/docs/drill-iceberg-metastore/", + "parent": "Drill Metastore", + "previous_title": "Drill Metastore", + "previous_url": "/docs/drill-metastore/", + "relative_path": "_docs/performance-tuning/drill-metastore/010-using-drill-metastore.md", + "title": "Using Drill Metastore", + "url": "/docs/using-drill-metastore/" + }, "Using Drill with BI Tools": { "breadcrumbs": [ { @@ -19835,8 +19997,8 @@ } ], "children": [], - "next_title": "Identifying Performance Issues", - "next_url": "/docs/identifying-performance-issues/", + "next_title": "Drill Metastore", + "next_url": "/docs/drill-metastore/", "parent": "Query Plans and Tuning", "previous_title": "Throttling", "previous_url": "/docs/throttling/", @@ -19865,6 +20027,66 @@ { "breadcrumbs": [ { + "title": "Drill Metastore", + "url": "/docs/drill-metastore/" + }, + { + "title": "Performance Tuning", + "url": "/docs/performance-tuning/" + } + ], + "children": [], + "next_title": "Drill Iceberg Metastore", + "next_url": "/docs/drill-iceberg-metastore/", + "parent": "Drill Metastore", + "previous_title": "Drill Metastore", + "previous_url": "/docs/drill-metastore/", + "relative_path": "_docs/performance-tuning/drill-metastore/010-using-drill-metastore.md", + "title": "Using Drill Metastore", + "url": "/docs/using-drill-metastore/" + }, + { + "breadcrumbs": [ + { + "title": "Drill Metastore", + "url": "/docs/drill-metastore/" + }, + { + "title": "Performance Tuning", + "url": "/docs/performance-tuning/" + } + ], + "children": [], + "next_title": "Identifying Performance Issues", + "next_url": "/docs/identifying-performance-issues/", + "parent": "Drill Metastore", + "previous_title": "Using Drill Metastore", + "previous_url": "/docs/using-drill-metastore/", + "relative_path": "_docs/performance-tuning/drill-metastore/020-drill-iceberg-metastore.md", + "title": "Drill Iceberg Metastore", + "url": "/docs/drill-iceberg-metastore/" + } + ], + "next_title": "Using Drill Metastore", + "next_url": "/docs/using-drill-metastore/", + "parent": "Performance Tuning", + "previous_title": "Controlling Parallelization to Balance Performance with Multi-Tenancy", + "previous_url": "/docs/controlling-parallelization-to-balance-performance-with-multi-tenancy/", + "relative_path": "_docs/performance-tuning/050-drill-metastore.md", + "title": "Drill Metastore", + "url": "/docs/drill-metastore/" + }, + { + "breadcrumbs": [ + { + "title": "Performance Tuning", + "url": "/docs/performance-tuning/" + } + ], + "children": [ + { + "breadcrumbs": [ + { "title": "Identifying Performance Issues", "url": "/docs/identifying-performance-issues/" }, @@ -19908,8 +20130,8 @@ "next_title": "Query Plans", "next_url": "/docs/query-plans/", "parent": "Performance Tuning", - "previous_title": "Controlling Parallelization to Balance Performance with Multi-Tenancy", - "previous_url": "/docs/controlling-parallelization-to-balance-performance-with-multi-tenancy/", + "previous_title": "Drill Iceberg Metastore", + "previous_url": "/docs/drill-iceberg-metastore/", "relative_path": "_docs/performance-tuning/050-identifying-performance-issues.md", "title": "Identifying Performance Issues", "url": "/docs/identifying-performance-issues/" diff --git a/_docs/performance-tuning/005-drill-metastore.md b/_docs/performance-tuning/005-drill-metastore.md new file mode 100644 index 0000000..3ff1a64 --- /dev/null +++ b/_docs/performance-tuning/005-drill-metastore.md @@ -0,0 +1,5 @@ +--- +title: "Drill Metastore" +parent: "Performance Tuning" +date: +--- diff --git a/_docs/performance-tuning/drill-metastore/010-using-drill-metastore.md b/_docs/performance-tuning/drill-metastore/010-using-drill-metastore.md new file mode 100644 index 0000000..d1dceb7 --- /dev/null +++ b/_docs/performance-tuning/drill-metastore/010-using-drill-metastore.md @@ -0,0 +1,378 @@ +--- +title: "Using Drill Metastore" +parent: "Drill Metastore" +date: 2020-01-30 +--- + +Drill 1.17 introduces the Drill Metastore which stores the table schema and table statistics. Statistics allow Drill to better create optimal query plans. + +The Metastore is an Beta feature; it is subject to change. We encourage you to try it and provide feedback. +Because the Metastore is in Beta, the SQL commands and Metastore formats may change in the next release. +{% include startnote.html %}In Drill 1.17, this feature is supported for Parquet tables only and is disabled by default.{% include endnote.html %} + +## Enabling Drill Metastore + +To use the Drill Metastore, you must enable it at the session or system level with one of the following commands: + + SET `metastore.enabled` = true; + ALTER SYSTEM SET `metastore.enabled` = true; + +Alternatively, you can enable the option in the Drill Web UI at `http://<drill-hostname-or-ip-address>:8047/options`. + +## Computing and storing table metadata to Drill Metastore + +Once you enable the Metastore, the next step is to populate it with data. Drill can query a table whether that table + has a Metastore entry or not. (If you are familiar with Hive, then you know that Hive requires that all tables have + Hive Metastore entries before you can query them.) In Drill, only add data to the Metastore when doing so improves + query performance. In general, large tables benefit from statistics more than small tables do. + +Unlike Hive, Drill does not require you to declare a schema. Instead, Drill infers the schema by scanning your table + and computes some metadata like MIN / MAX column values and NULLS COUNT designated as "metadata" to be able to + produce more optimizations like filter push-down, etc. If `planner.statistics.use` option is enabled, this command + will also calculate and store table statistics into Drill Metastore. + +Unlike Hive, Drill does not require you to declare a schema. Instead, Drill infers the schema by scanning your table + in the same way as it is done during regular select. + +## Configuration + +Default Metastore configuration is defined in `drill-metastore-default.conf` file. +It can be overridden in `drill-metastore-override.conf`. Distribution configuration can be +indicated in `drill-metastore-distrib.conf`. + +All configuration properties should reside in `drill.metastore` namespace. +Metastore implementation based on class implementation config property `drill.metastore.implementation.class`. + +### Metastore Components + +Metastore can store metadata for various components: tables, views etc. +Current implementation provides fully functioning support for tables component. +Views component support is not implemented but contains stub methods to show +how new Metastore components like udfs, storage plugins, etc. be added in future. + +### Metastore Tables + +Metastore Tables component contains metadata about Drill tables, including general information, as well as +information about table segments, files, row groups, partitions. + +Full table metadata consists of two major concepts: general information and top-level segments metadata. +Table general information contains basic table information and corresponds to `BaseTableMetadata` class. + +Table can be non-partitioned and partitioned. Non-partitioned tables, have only one top-level segment +which is called default (`MetadataInfo#DEFAULT_SEGMENT_KEY`). Partitioned tables may have several top-level segments. +Each top-level segment can include metadata about inner segments, files, row groups and partitions. + +Unique table identifier in Metastore Tables is combination of storage plugin, workspace and table name. +Table metadata inside is grouped by top-level segments, unique identifier of the top-level segment and its metadata +is storage plugin, workspace, table name and metadata key. + +### Related Session/System Options + +The following options are set via `ALTER SYSTEM SET`, or `ALTER SESSION SET` or via the Drill Web console. + +- **metastore.enabled** +Enables Drill Metastore usage to be able to store table metadata during ANALYZE TABLE commands execution and to be able + to read table metadata during regular queries execution or when querying some INFORMATION_SCHEMA tables. Default is `false`. +- **metastore.metadata.store.depth_level** +Specifies maximum level depth for collecting metadata. Same options as the _level_ option above. Default is `'ALL'`. +- **metastore.retrieval.retry_attempts** +If you run the `ANALYZE TABLE` command at the same time as queries run, then the query can read incorrect or corrupt statistics. +Drill will reload statistics and replan the query. This option specifies the maximum number of retry attempts. Default is `5`. +- **metastore.metadata.fallback_to_file_metadata** +Allows using [file metadata cache]({{site.baseurl}}/docs/refresh-table-metadata) for the case when required metadata is absent in the Metastore. +Default is `true`. +- **metastore.metadata.use_schema** +The `ANALYZE TABLE` command infers table schema as it gathers statistics. This option tells Drill to use that schema information while planning the query. +Disable this option if Drill has inferred the schema incorrectly, or schema will be provided separately (see [CREATE OR REPLACE SCHEMA]({{site.baseurl}}/docs/create-or-replace-schema)). +Default is `true`. +- **metastore.metadata.use_statistics** +Enables obtaining table and column statistics, stored in the Metastore, at the planning stage. Default is `true`. +Enable `planner.statistics.use` to be able to use statistics during query planning. +- **metastore.metadata.ctas.auto-collect** +Drill provides the [`CREATE TABLE AS`]({{site.baseurl}}/docs/create-or-replace-schema) commands to create new tables. +This option causes Drill to gather schema and statistics for those tables automatically as they are written. +This option is not active for now. Possible values: `'ALL'`, `'SCHEMA'`, `'NONE'`. Default is `'NONE'`. +- **drill.exec.storage.implicit.last_modified_time.column.label** +Sets the implicit column name for the last modified time (`lmt`) column. Used when producing Metastore analyze. +- **drill.exec.storage.implicit.row_group_index.column.label** +Sets the implicit column name for the row group index (`rgi`) column. Used when producing Metastore analyze. +- **drill.exec.storage.implicit.row_group_length.column.label** +Sets the implicit column name for the row group length (`rgl`) column. Used when producing Metastore analyze. +- **drill.exec.storage.implicit.row_group_start.column.label** +Sets the implicit column name for the row group start (`rgs`) column. Used when producing Metastore analyze. + +## Incremental analysis + +If you have computed statistics for a table, and issue `ANALYZE TABLE` a second time, Drill will attempt to update statistics, called "incremental analysis." +Incremental analysis will compute metadata only for files and partitions changed since the last analysis and reuse + actual metadata from the Metastore where possible. + +Drill performs incremental analysis only when the `ANALYZE TABLE command` is identical to the previous command: +- The list of columns in the `COLUMNS` clause is a subset of interesting columns from the previous run. +- The metadata level in the LEVEL clause must be the same as the previous run. + +If either of these two conditions is false, Drill will perform a full analysis over the entire table. + +## General Information + +- Drill 1.17 supports the Metastore and `ANALYZE TABLE` only for tables stored as Parquet files and only when stored in the `DFS` storage plugin. +- The first time you execute ANALYZE TABLE for a table, Drill will scan the entire tables (all files.) +When you next issue the same command, Drill will scan only those files added since the previous run. +The command will return the message if table statistics are up-to-date: + + +``` +apache drill (dfs.tmp)> analyze table lineitem refresh metadata; ++-------+---------------------------------------------------------+ +| ok | summary | ++-------+---------------------------------------------------------+ +| false | Table metadata is up to date, analyze wasn't performed. | ++-------+---------------------------------------------------------+ +``` + +### Metadata usage + +Drill uses the Metastore in several places. When you run a query with multiple directories, files or Parquet row groups, +Drill will use statistics to "prune" the scan. That is, to identify those directories, files or row groups which +do not contain data which your query needs. If you add new files or directories, and do not rerun `ANALYZE TABLE`, +then Drill will assume that existing metadata is invalid and wouldn't use it. Periodically rerun `ANALYZE TABLE` so that + Drill can use table metadata when possible. + +### Limitations + +This feature is currently in the alpha phase (preview, experimental) for Drill 1.17 and only applies to Parquet + tables in this release. You must enable this feature through the `metastore.enabled` system/session option. + +## Examples + +Examples throughout this topic use the files and directories described in the following section, Directory and File Setup. + +### Directory and File Setup + +Download [TPC-H sf1 tables](https://s3-us-west-1.amazonaws.com/drill-public/tpch/sf1/tpch_sf1_parquet.tar.gz) and unpack + archive. + +Create lineitem directory in `/tmp/` and two subdirectories under `/tmp/lineitem` named `s1` and `s2` and copy there table data: + + mkdir /tmp/lineitem + mkdir /tmp/lineitem/s1 + mkdir /tmp/lineitem/s2 + cp TPCH/lineitem /tmp/lineitem/s1 + cp TPCH/lineitem /tmp/lineitem/s2 + +Query the directory `/tmp/lineitem`: + +``` +SELECT count(*) FROM dfs.tmp.lineitem; ++----------+ +| EXPR$0 | ++----------+ +| 12002430 | ++----------+ +1 row selected (0.291 seconds) +``` + +Notice that the query plan contains group scan with `usedMetastore = false`: + + +``` +00-00 Screen : rowType = RecordType(BIGINT EXPR$0): rowcount = 1.0, cumulative cost = {2.1 rows, 2.1 cpu, 1.0 io, 0.0 network, 0.0 memory}, id = 8410 +00-01 Project(EXPR$0=[$0]) : rowType = RecordType(BIGINT EXPR$0): rowcount = 1.0, cumulative cost = {2.0 rows, 2.0 cpu, 1.0 io, 0.0 network, 0.0 memory}, id = 8409 +00-02 DirectScan(groupscan=[selectionRoot = file:/tmp/lineitem, numFiles = 12, usedMetadataSummaryFile = false, usedMetastore = false, ... +``` + +### Computing and storing table metadata to Drill Metastore + +Run [ANALYZE TABLE]({{site.baseurl}}/docs/analyze-table-refresh-metadata) command on the table, whose metadata should + be computed and stored into the Drill Metastore: + + +``` +apache drill> ANALYZE TABLE dfs.tmp.lineitem REFRESH METADATA; ++------+-------------------------------------------------------------+ +| ok | summary | ++------+-------------------------------------------------------------+ +| true | Collected / refreshed metadata for table [dfs.tmp.lineitem] | ++------+-------------------------------------------------------------+ +1 row selected (32.257 seconds) +``` + +The output of this command provides the status of the command execution and its summary. + +Once, its metadata is collected and stored, it will be used when querying the table. To ensure that it was used, its + info was added to the group scan (`usedMetastore=true` entry in `ParquetGroupScan`): + + +``` +00-00 Screen : rowType = RecordType(BIGINT EXPR$0): rowcount = 1.0, cumulative cost = {2.1 rows, 2.1 cpu, 1.0 io, 0.0 network, 0.0 memory}, id = 8560 +00-01 Project(EXPR$0=[$0]) : rowType = RecordType(BIGINT EXPR$0): rowcount = 1.0, cumulative cost = {2.0 rows, 2.0 cpu, 1.0 io, 0.0 network, 0.0 memory}, id = 8559 +00-02 DirectScan(groupscan=[selectionRoot = /tmp/lineitem, numFiles = 12, usedMetadataSummaryFile = false, usedMetastore = true, ... +``` + +### Performing incremental analysis + +Rerun [ANALYZE TABLE]({{site.baseurl}}/docs/analyze-table-refresh-metadata) command on the table with previously collected metadata: + + +``` +apache drill> ANALYZE TABLE dfs.tmp.lineitem REFRESH METADATA; ++-------+---------------------------------------------------------+ +| ok | summary | ++-------+---------------------------------------------------------+ +| false | Table metadata is up to date, analyze wasn't performed. | ++-------+---------------------------------------------------------+ +1 row selected (0.249 seconds) +``` + +### Exposing Drill Metastore metadata through `INFORMATION_SCHEMA` tables + +Drill exposes some Metastore tables metadata through `INFORMATION_SCHEMA` tables. + +`TABLES` table includes the set of tables on which you have run `ANALYZE TABLE`. +Description of Metastore-specific columns: + +|Column name |Type |Nullable |Description | +|-----------------------|-----------|-----------|-------------------------------------------------------------------------------------------------------| +|`TABLE_SOURCE` |VARCHAR |YES |Table data type: `PARQUET`, `CSV`, `JSON` | +|`LOCATION` |VARCHAR |YES |Table location: `/tmp/nation` | +|`NUM_ROWS` |BIGINT |YES |The total number of rows in all files of the table. Null if not known | +|`LAST_MODIFIED_TIME` |TIMESTAMP |YES |Timestamp of the most-recently modified file within the table. Updated on each `ANALYZE TABLE` run. | + +Example of its content: + +``` +apache drill> SELECT * FROM INFORMATION_SCHEMA.`TABLES` WHERE TABLE_NAME='lineitem'; ++---------------+--------------+------------+------------+--------------+---------------+----------+-----------------------+ +| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | TABLE_SOURCE | LOCATION | NUM_ROWS | LAST_MODIFIED_TIME | ++---------------+--------------+------------+------------+--------------+---------------+----------+-----------------------+ +| DRILL | dfs.tmp | lineitem | TABLE | PARQUET | /tmp/lineitem | 12002430 | 2016-09-28 03:22:58.0 | ++---------------+--------------+------------+------------+--------------+---------------+----------+-----------------------+ +1 row selected (0.157 seconds) +``` + +The `COLUMNS` table describes the columns within each table. Only those columns listed in the `COLUMNS` clause of the + `ANALYZE TABLE` statement appear in this table. + +|Column name |Type |Nullable |Description | +|-----------------------|-----------|-----------|-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| +|`COLUMN_DEFAULT` |VARCHAR |YES |Column default value. | +|`COLUMN_FORMAT` |VARCHAR |YES |Usually applicable for date time columns: `yyyy-MM-dd`. See [Format for Date, Time Conversion]({{site.baseurl}}/docs/create-or-replace-schema/#format-for-date-time-conversion). | +|`NUM_NULLS` |BIGINT |YES |The number of rows which contain nulls for this column. | +|`MIN_VAL` |VARCHAR |YES |The minimum value of the column expressed as a string. For example: `'-273'`. | +|`MAX_VAL` |VARCHAR |YES |The maximum value of the column expressed as a string. For example: `'100500'`. | +|`NDV` |FLOAT8 |YES |Number of distinct values in column, expressed in Double. | +|`EST_NUM_NON_NULLS` |FLOAT8 |YES |Estimated number of non null values, expressed in Double. | +|`IS_NESTED` |BIT |NO |If column is nested. Nested columns are extracted from columns with struct type. | + +Example of its content: + +``` +apache drill> SELECT * FROM INFORMATION_SCHEMA.`COLUMNS` WHERE TABLE_NAME='lineitem'; ++---------------+--------------+------------+-----------------+------------------+----------------+-------------+-------------------+--------------------------+------------------------+-------------------+-------------------------+---------------+--------------------+---------------+--------------------+-------------+---------------+-----------+--------------+---------------------------------------------+-----------+-------------------+-----------+ +| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | COLUMN_DEFAULT | IS_NULLABLE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_PRECISION_RADIX | NUMERIC_SCALE | DATETIME_PRECISION | INTERVAL_TYPE | INTERVAL_PRECISION | COLUMN_SIZE | COLUMN_FORMAT | NUM_NULLS | MIN_VAL | MAX_VAL | NDV | EST_NUM_NON_NULLS | IS_NESTED | ++---------------+--------------+------------+-----------------+------------------+----------------+-------------+-------------------+--------------------------+------------------------+-------------------+-------------------------+---------------+--------------------+---------------+--------------------+-------------+---------------+-----------+--------------+---------------------------------------------+-----------+-------------------+-----------+ +| DRILL | dfs.tmp | lineitem | dir0 | 1 | null | YES | CHARACTER VARYING | 65535 | 65535 | null | null | null | null | null | null | 65535 | null | 0 | s1 | s2 | null | null | false | +| DRILL | dfs.tmp | lineitem | l_orderkey | 2 | null | YES | INTEGER | null | null | 0 | 2 | 0 | null | null | null | 11 | null | 0 | 1 | 6000000 | 1499876.0 | 1.200243E7 | false | +| DRILL | dfs.tmp | lineitem | l_partkey | 3 | null | YES | INTEGER | null | null | 0 | 2 | 0 | null | null | null | 11 | null | 0 | 1 | 200000 | 199857.0 | 1.200243E7 | false | +| DRILL | dfs.tmp | lineitem | l_suppkey | 4 | null | YES | INTEGER | null | null | 0 | 2 | 0 | null | null | null | 11 | null | 0 | 1 | 10000 | 10001.0 | 1.200243E7 | false | +| DRILL | dfs.tmp | lineitem | l_linenumber | 5 | null | YES | INTEGER | null | null | 0 | 2 | 0 | null | null | null | 11 | null | 0 | 1 | 7 | 7.0 | 1.200243E7 | false | +| DRILL | dfs.tmp | lineitem | l_quantity | 6 | null | YES | DOUBLE | null | null | 0 | 2 | 0 | null | null | null | 24 | null | 0 | 1.0 | 50.0 | 50.0 | 1.200243E7 | false | +| DRILL | dfs.tmp | lineitem | l_extendedprice | 7 | null | YES | DOUBLE | null | null | 0 | 2 | 0 | null | null | null | 24 | null | 0 | 901.0 | 104949.5 | 933142.0 | 1.200243E7 | false | +| DRILL | dfs.tmp | lineitem | l_discount | 8 | null | YES | DOUBLE | null | null | 0 | 2 | 0 | null | null | null | 24 | null | 0 | 0.0 | 0.1 | 11.0 | 1.200243E7 | false | +| DRILL | dfs.tmp | lineitem | l_tax | 9 | null | YES | DOUBLE | null | null | 0 | 2 | 0 | null | null | null | 24 | null | 0 | 0.0 | 0.08 | 9.0 | 1.200243E7 | false | +| DRILL | dfs.tmp | lineitem | l_returnflag | 10 | null | YES | CHARACTER VARYING | 65535 | 65535 | null | null | null | null | null | null | 65535 | null | 0 | A | R | 3.0 | 1.200243E7 | false | +| DRILL | dfs.tmp | lineitem | l_linestatus | 11 | null | YES | CHARACTER VARYING | 65535 | 65535 | null | null | null | null | null | null | 65535 | null | 0 | F | O | 2.0 | 1.200243E7 | false | +| DRILL | dfs.tmp | lineitem | l_shipdate | 12 | null | YES | DATE | null | null | null | null | null | 10 | null | null | 10 | null | 0 | 694310400000 | 912470400000 | 2526.0 | 1.200243E7 | false | +| DRILL | dfs.tmp | lineitem | l_commitdate | 13 | null | YES | DATE | null | null | null | null | null | 10 | null | null | 10 | null | 0 | 696816000000 | 909792000000 | 2466.0 | 1.200243E7 | false | +| DRILL | dfs.tmp | lineitem | l_receiptdate | 14 | null | YES | DATE | null | null | null | null | null | 10 | null | null | 10 | null | 0 | 694483200000 | 915062400000 | 2554.0 | 1.200243E7 | false | +| DRILL | dfs.tmp | lineitem | l_shipinstruct | 15 | null | YES | CHARACTER VARYING | 65535 | 65535 | null | null | null | null | null | null | 65535 | null | 0 | COLLECT COD | TAKE BACK RETURN | 4.0 | 1.200243E7 | false | +| DRILL | dfs.tmp | lineitem | l_shipmode | 16 | null | YES | CHARACTER VARYING | 65535 | 65535 | null | null | null | null | null | null | 65535 | null | 0 | AIR | TRUCK | 7.0 | 1.200243E7 | false | +| DRILL | dfs.tmp | lineitem | l_comment | 17 | null | YES | CHARACTER VARYING | 65535 | 65535 | null | null | null | null | null | null | 65535 | null | 0 | Tiresias | zzle? slyly final platelets sleep quickly. | 4586320.0 | 1.200243E7 | false | ++---------------+--------------+------------+-----------------+------------------+----------------+-------------+-------------------+--------------------------+------------------------+-------------------+-------------------------+---------------+--------------------+---------------+--------------------+-------------+---------------+-----------+--------------+---------------------------------------------+-----------+-------------------+-----------+ +17 rows selected (0.187 seconds) +``` + +A table can be divided into directories, called "partitions". The `PARTITIONS` table contains an entry for each directory. + +|Column name |Type |Nullable |Description | +|-----------------------|-----------|-----------|-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| +|`TABLE_CATALOG` |VARCHAR |YES |Table catalog (currently we have only one catalog): `DRILL`. | +|`TABLE_SCHEMA` |VARCHAR |YES |Table schema: `dfs.tmp`. | +|`TABLE_NAME` |VARCHAR |YES |Table name: `nation`. | +|`METADATA_KEY` |VARCHAR |YES |Top level segment key, the same for all nested segments and partitions: `part_int=3`. | +|`METADATA_TYPE` |VARCHAR |YES |`SEGMENT` or `PARTITION`. Partition here corresponds to "Drill partition", though segment corresponds to data parts like partitions in general case, for example, Hive partition. | +|`METADATA_IDENTIFIER` |VARCHAR |YES |Current metadata identifier: `part_int=3/part_varchar=g`. It is unique value for segment or partition within the table. | +|`PARTITION_COLUMN` |VARCHAR |YES |Partition column name: `part_varchar`. | +|`PARTITION_VALUE` |VARCHAR |YES |Partition column value: `g`. | +|`LOCATION` |VARCHAR |YES |Segment location, `null` for partitions: `/tmp/nation/part_int=3`. | +|`LAST_MODIFIED_TIME` |TIMESTAMP |YES |Last modification time. | + +Example of its content: + +``` +apache drill (information_schema)> SELECT * FROM INFORMATION_SCHEMA.`PARTITIONS` WHERE TABLE_NAME='lineitem'; ++---------------+--------------+------------+--------------+---------------+---------------------+------------------+-----------------+------------------+-----------------------+ +| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | METADATA_KEY | METADATA_TYPE | METADATA_IDENTIFIER | PARTITION_COLUMN | PARTITION_VALUE | LOCATION | LAST_MODIFIED_TIME | ++---------------+--------------+------------+--------------+---------------+---------------------+------------------+-----------------+------------------+-----------------------+ +| DRILL | dfs.tmp | lineitem | s2 | SEGMENT | s2 | `dir0` | s2 | /tmp/lineitem/s2 | 2016-09-28 03:22:58.0 | +| DRILL | dfs.tmp | lineitem | s1 | SEGMENT | s1 | `dir0` | s1 | /tmp/lineitem/s1 | 2016-09-28 03:22:58.0 | ++---------------+--------------+------------+--------------+---------------+---------------------+------------------+-----------------+------------------+-----------------------+ +2 rows selected (0.149 seconds) +``` + +### Dropping table metadata + +Table metadata may be dropped using `ANALYZE TABLE DROP METADATA` command: + +``` +apache drill> ANALYZE TABLE dfs.tmp.lineitem DROP METADATA; ++------+----------------------------------------+ +| ok | summary | ++------+----------------------------------------+ +| true | Metadata for table [lineitem] dropped. | ++------+----------------------------------------+ +1 row selected (0.291 seconds) +``` + +### Collecting metadata for specific set of columns + +For the case when metadata for several columns should be computed and stored into the Metastore, the following command may be used: + +``` +apache drill (information_schema)> ANALYZE TABLE dfs.tmp.lineitem COLUMNS(l_orderkey, l_partkey) REFRESH METADATA; ++------+-------------------------------------------------------------+ +| ok | summary | ++------+-------------------------------------------------------------+ +| true | Collected / refreshed metadata for table [dfs.tmp.lineitem] | ++------+-------------------------------------------------------------+ +1 row selected (94.1 seconds) +``` + +Now, check, that metadata is collected only for specified columns (`MIN_VAL`, `MAX_VAL`, `NDV`, etc.), but all + columns are present: + +``` +apache drill (information_schema)> SELECT * FROM INFORMATION_SCHEMA.`COLUMNS` WHERE TABLE_NAME='lineitem'; ++---------------+--------------+------------+-----------------+------------------+----------------+-------------+-------------------+--------------------------+------------------------+-------------------+-------------------------+---------------+--------------------+---------------+--------------------+-------------+---------------+-----------+---------+---------+-----------+-------------------+-----------+ +| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | COLUMN_DEFAULT | IS_NULLABLE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_PRECISION_RADIX | NUMERIC_SCALE | DATETIME_PRECISION | INTERVAL_TYPE | INTERVAL_PRECISION | COLUMN_SIZE | COLUMN_FORMAT | NUM_NULLS | MIN_VAL | MAX_VAL | NDV | EST_NUM_NON_NULLS | IS_NESTED | ++---------------+--------------+------------+-----------------+------------------+----------------+-------------+-------------------+--------------------------+------------------------+-------------------+-------------------------+---------------+--------------------+---------------+--------------------+-------------+---------------+-----------+---------+---------+-----------+-------------------+-----------+ +| DRILL | dfs.tmp | lineitem | dir0 | 1 | null | YES | CHARACTER VARYING | 65535 | 65535 | null | null | null | null | null | null | 65535 | null | 0 | s1 | s2 | null | null | false | +| DRILL | dfs.tmp | lineitem | l_orderkey | 2 | null | YES | INTEGER | null | null | 0 | 2 | 0 | null | null | null | 11 | null | 0 | 1 | 6000000 | 1499876.0 | 1.200243E7 | false | +| DRILL | dfs.tmp | lineitem | l_partkey | 3 | null | YES | INTEGER | null | null | 0 | 2 | 0 | null | null | null | 11 | null | 0 | 1 | 200000 | 199857.0 | 1.200243E7 | false | +| DRILL | dfs.tmp | lineitem | l_suppkey | 4 | null | YES | INTEGER | null | null | 0 | 2 | 0 | null | null | null | 11 | null | null | null | null | null | null | false | +| DRILL | dfs.tmp | lineitem | l_linenumber | 5 | null | YES | INTEGER | null | null | 0 | 2 | 0 | null | null | null | 11 | null | null | null | null | null | null | false | +| DRILL | dfs.tmp | lineitem | l_quantity | 6 | null | YES | DOUBLE | null | null | 0 | 2 | 0 | null | null | null | 24 | null | null | null | null | null | null | false | +| DRILL | dfs.tmp | lineitem | l_extendedprice | 7 | null | YES | DOUBLE | null | null | 0 | 2 | 0 | null | null | null | 24 | null | null | null | null | null | null | false | +| DRILL | dfs.tmp | lineitem | l_discount | 8 | null | YES | DOUBLE | null | null | 0 | 2 | 0 | null | null | null | 24 | null | null | null | null | null | null | false | +| DRILL | dfs.tmp | lineitem | l_tax | 9 | null | YES | DOUBLE | null | null | 0 | 2 | 0 | null | null | null | 24 | null | null | null | null | null | null | false | +| DRILL | dfs.tmp | lineitem | l_returnflag | 10 | null | YES | CHARACTER VARYING | 65535 | 65535 | null | null | null | null | null | null | 65535 | null | null | null | null | null | null | false | +| DRILL | dfs.tmp | lineitem | l_linestatus | 11 | null | YES | CHARACTER VARYING | 65535 | 65535 | null | null | null | null | null | null | 65535 | null | null | null | null | null | null | false | +| DRILL | dfs.tmp | lineitem | l_shipdate | 12 | null | YES | DATE | null | null | null | null | null | 10 | null | null | 10 | null | null | null | null | null | null | false | +| DRILL | dfs.tmp | lineitem | l_commitdate | 13 | null | YES | DATE | null | null | null | null | null | 10 | null | null | 10 | null | null | null | null | null | null | false | +| DRILL | dfs.tmp | lineitem | l_receiptdate | 14 | null | YES | DATE | null | null | null | null | null | 10 | null | null | 10 | null | null | null | null | null | null | false | +| DRILL | dfs.tmp | lineitem | l_shipinstruct | 15 | null | YES | CHARACTER VARYING | 65535 | 65535 | null | null | null | null | null | null | 65535 | null | null | null | null | null | null | false | +| DRILL | dfs.tmp | lineitem | l_shipmode | 16 | null | YES | CHARACTER VARYING | 65535 | 65535 | null | null | null | null | null | null | 65535 | null | null | null | null | null | null | false | +| DRILL | dfs.tmp | lineitem | l_comment | 17 | null | YES | CHARACTER VARYING | 65535 | 65535 | null | null | null | null | null | null | 65535 | null | null | null | null | null | null | false | ++---------------+--------------+------------+-----------------+------------------+----------------+-------------+-------------------+--------------------------+------------------------+-------------------+-------------------------+---------------+--------------------+---------------+--------------------+-------------+---------------+-----------+---------+---------+-----------+-------------------+-----------+ +17 rows selected (0.183 seconds) +``` diff --git a/_docs/performance-tuning/drill-metastore/030-drill-iceberg-metastore.md b/_docs/performance-tuning/drill-metastore/030-drill-iceberg-metastore.md new file mode 100644 index 0000000..1294286 --- /dev/null +++ b/_docs/performance-tuning/drill-metastore/030-drill-iceberg-metastore.md @@ -0,0 +1,63 @@ +--- +title: "Drill Iceberg Metastore" +parent: "Drill Metastore" +date: +--- + +Drill uses Iceberg Metastore implementation based on [Iceberg tables](http://iceberg.incubator.apache.org). For + details on how to configure Iceberg Metastore implementation and its option descriptions, please refer to + [Iceberg Metastore docs](https://github.com/apache/drill/blob/master/metastore/iceberg-metastore/README.md). + +### Iceberg Tables Location + +Iceberg tables will reside on the file system in the location based on +Iceberg Metastore base location `drill.metastore.iceberg.location.base_path` and component specific location. +If Iceberg Metastore base location is `/drill/metastore/iceberg` +and tables component location is `tables`. Iceberg table for tables component +will be located in `/drill/metastore/iceberg/tables` folder. + +Metastore metadata will be stored inside Iceberg table location provided +in the configuration file. Drill table metadata location will be constructed +based on specific component storage keys. For example, for `tables` component, +storage keys are storage plugin, workspace and table name: unique table identifier in Drill. + +Assume Iceberg table location is `/drill/metastore/iceberg/tables`, metadata for the table +`dfs.tmp.nation` will be stored in the `/drill/metastore/iceberg/tables/dfs/tmp/nation` folder. + +Example of base Metastore configuration file `drill-metastore-override.conf`, where Iceberg tables will be stored in + hdfs: + +``` +drill.metastore.iceberg: { + config.properties: { + fs.defaultFS: "hdfs:///" + } + + location: { + base_path: "/drill/metastore", + relative_path: "iceberg" + } +} +``` + +### Metadata Storage Format + +Iceberg tables support data storage in three formats: Parquet, Avro, ORC. Drill metadata will be stored in Parquet files. +This format was chosen over others since it is column oriented and efficient in terms of disk I/O when specific +columns need to be queried. + +Each Parquet file will hold information for one partition. Partition keys will depend on Metastore +component characteristics. For example, for tables component, partitions keys are storage plugin, workspace, +table name and metadata key. + +Parquet files name will be based on UUID to ensure uniqueness. If somehow collision occurs, modify operation +in Metastore will fail. + +### Iceberg metadata expiration + +Iceberg table generates metadata for each modification operation: +snapshot, manifest file, table metadata file. Also when performing delete operation, +previously stored data files are not deleted. These files with the time can occupy lots of space. +Two table properties `write.metadata.delete-after-commit.enabled` and `write.metadata.previous-versions-max` +control expiration process. Metadata files will be expired automatically if +`write.metadata.delete-after-commit.enabled` is enabled. diff --git a/_docs/query-data/060-querying-the-information-schema.md b/_docs/query-data/060-querying-the-information-schema.md index d4e07fe..4e22ba3 100644 --- a/_docs/query-data/060-querying-the-information-schema.md +++ b/_docs/query-data/060-querying-the-information-schema.md @@ -1,6 +1,6 @@ --- title: "Querying the INFORMATION SCHEMA" -date: 2019-04-05 +date: 2020-01-30 parent: "Query Data" --- @@ -17,6 +17,7 @@ context. You can query the following INFORMATION_SCHEMA tables: * COLUMNS * VIEWS * FILES + * PARTITIONS ## SCHEMATA @@ -73,7 +74,11 @@ type) for each column in each table or view. The VIEWS table returns the name and definition for each view in your databases. Note that file schemas are the canonical repository for views in Drill. Depending on how you create a view, the may only be displayed in Drill -after it has been used. +after it has been used. + +## PARTITIONS + +The PARTITIONS table returns information about table partitions including partition columns, partition keys, etc. for each table. ## FILES diff --git a/_docs/sql-reference/sql-commands/005-supported-sql-commands.md b/_docs/sql-reference/sql-commands/005-supported-sql-commands.md index 9d242e9..c7f5da2 100644 --- a/_docs/sql-reference/sql-commands/005-supported-sql-commands.md +++ b/_docs/sql-reference/sql-commands/005-supported-sql-commands.md @@ -1,29 +1,31 @@ --- title: "Supported SQL Commands" -date: 2018-02-09 00:16:05 UTC +date: 2020-01-28 parent: "SQL Commands" --- The following table provides a list of the SQL commands that Drill supports, with their descriptions and example syntax: -| Command | Description | Syntax | -|--------------------------------------------------------------------------|------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|--------------------------------------------------------------------------------------------------------------------| -| [ALTER SESSION SET]({{site.baseurl}}/docs/set) | The SET command replaces the ALTER SESSION SET command in Drill version 1.3 and later. ALTER SESSION is now just an alias for the SET command. See [SET]({{site.baseurl}}/docs/set). | [ALTER SESSION] SET \`option_name` = value; | -| [ALTER SYSTEM SET]({{site.baseurl}}/docs/alter-system) | Changes a system setting. The new setting persists across all sessions. For a list of Drill options and their descriptions, see [Planning and Execution Options]{{site.baseurl/docs/planning-and-execution-options/}}. | ALTER SYSTEM SET \`option_name` = value; | -| [ALTER SYSTEM RESET]({{site.baseurl}}/docs/alter-system) | Changes a system setting back to its default system setting. For a list of Drill options and their descriptions, see [Planning and Execution Options]{{site.baseurl/docs/planning-and-execution-options/}}. | ALTER SYSTEM RESET \`option_name`; | -| [ALTER SYSTEM RESET ALL]({{site.baseurl}}/docs/alter-system) | Changes all system settings back to their default system values. For a list of Drill options and their descriptions, see [Planning and Execution Options]{{site.baseurl/docs/planning-and-execution-options/}}. | ALTER SYSTEM RESET ALL; | -| [CREATE TABLE AS(CTAS)]({{site.baseurl}}/docs/create-table-as-ctas/) | Creates a new table and populates the new table with rows returned from a SELECT query. Use the CREATE TABLE AS (CTAS) statement in place of INSERT INTO. | CREATE TABLE name AS query; | -| [CREATE TEMPORARY TABLE AS(CTTAS)]({{site.baseurl}}/docs/create-temp-table-as-cttas/) | Stores the results of a query in a temporary table. | CREATE TEMPORARY TABLE name AS query; | -| [CREATE VIEW]({{site.baseurl}}/docs/create-view) | Creates a virtual structure for the result set of a stored query.- | CREATE [OR REPLACE] VIEW [workspace.]view_name [ (column_name [, ...]) ] AS query; | -| [DESCRIBE ]({{site.baseurl}}/docs/describe) | Returns information about columns in a table or view. | DESCRIBE [workspace.]table_name | -| [DROP VIEW]({{site.baseurl}}/docs/drop-view) | Removes a view. | DROP VIEW [workspace.]view_name ; | -| [EXPLAIN PLAN FOR]({{site.baseurl}}/docs/explain) | Returns the physical plan for a particular query. | EXPLAIN PLAN FOR query; | -| [EXPLAIN PLAN WITHOUT IMPLEMENTATION FOR]({{site.baseurl}}/docs/explain) | Returns the logical plan for a particular query. | EXPLAIN PLAN WITHOUT IMPLEMENTATION FOR query; | -| [RESET]({{site.baseurl}}/docs/reset) | Changes a session setting back to its default system setting. For a list of Drill options and their descriptions, see [Planning and Execution Options]{{site.baseurl/docs/planning-and-execution-options/}}. | [ALTER SESSION] RESET \`option_name`; | -| [SELECT]({{site.baseurl}}/docs/select) | Retrieves data from tables and files. | [WITH subquery]SELECT column_list FROM table_name[WHERE clause] [GROUP BY clause][HAVING clause][ORDER BY clause]; | -| [SET]({{site.baseurl}}/docs/set) | Changes a system setting for the duration of a session. A session ends when you quit the Drill shell. For a list of Drill options and their descriptions, see [Planning and Execution Options]{{site.baseurl/docs/planning-and-execution-options/}}. | [ALTER SESSION] SET \`option_name` = value; | -| [SHOW DATABASES]({{site.baseurl}}/docs/show-databases-and-show-schemas) | Returns a list of available schemas. Equivalent to SHOW SCHEMAS. | SHOW DATABASES; | -| [SHOW FILES]({{site.baseurl}}/docs/show-files) | Returns a list of files in a file system schema. | SHOW FILES IN|FROM filesystem.\`schema_name`; | -| [SHOW SCHEMAS]({{site.baseurl}}/docs/show-databases-and-show-schemas) | Returns a list of available schemas. Equivalent to SHOW DATABASES. | SHOW SCHEMAS; | -| [SHOW TABLES]({{site.baseurl}}/docs/show-tables) | Returns a list of tables and views. | SHOW TABLES; | -| [USE]({{site.baseurl}}/docs/use) | Change to a particular schema. When you opt to use a particular schema, Drill issues queries on that schema only. | USE schema_name; | [...] +| Command | Description | Syntax | +|-----------------------------------------------------------------------------------------------|------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|--------------------------------------------------------------------------------------------------------------------| +| [ALTER SESSION SET]({{site.baseurl}}/docs/set) | The SET command replaces the ALTER SESSION SET command in Drill version 1.3 and later. ALTER SESSION is now just an alias for the SET command. See [SET]({{site.baseurl}}/docs/set). | [ALTER SESSION] SET \`option_name` = value; | +| [ANALYZE TABLE REFRESH METADATA]({{site.baseurl}}/docs/analyze-table-refresh-metadata) | Computes and stores the table schema and table statistics to the Drill Metastore. | ANALYZE TABLE [table_name] [COLUMNS {(col1, col2, ...) | NONE}] REFRESH METADATA ['level' LEVEL] [{COMPUTE | ESTIMATE} | STATISTICS [...] +| [ANALYZE TABLE COMPUTE STATISTICS]({{site.baseurl}}/docs/analyze-table-compute-statistics) | Computes statistics on Parquet data stored in tables and writes it to a JSON file in the `.stats.drill` directory. | ANALYZE TABLE [workspace.]table_name COMPUTE STATISTICS [(column1, column2,...)] [SAMPLE number PERCENT] | +| [ALTER SYSTEM SET]({{site.baseurl}}/docs/alter-system) | Changes a system setting. The new setting persists across all sessions. For a list of Drill options and their descriptions, see [Planning and Execution Options]{{site.baseurl/docs/planning-and-execution-options/}}. | ALTER SYSTEM SET \`option_name` = value; | +| [ALTER SYSTEM RESET]({{site.baseurl}}/docs/alter-system) | Changes a system setting back to its default system setting. For a list of Drill options and their descriptions, see [Planning and Execution Options]{{site.baseurl/docs/planning-and-execution-options/}}. | ALTER SYSTEM RESET \`option_name`; | +| [ALTER SYSTEM RESET ALL]({{site.baseurl}}/docs/alter-system) | Changes all system settings back to their default system values. For a list of Drill options and their descriptions, see [Planning and Execution Options]{{site.baseurl/docs/planning-and-execution-options/}}. | ALTER SYSTEM RESET ALL; | +| [CREATE TABLE AS(CTAS)]({{site.baseurl}}/docs/create-table-as-ctas/) | Creates a new table and populates the new table with rows returned from a SELECT query. Use the CREATE TABLE AS (CTAS) statement in place of INSERT INTO. | CREATE TABLE name AS query; | +| [CREATE TEMPORARY TABLE AS(CTTAS)]({{site.baseurl}}/docs/create-temp-table-as-cttas/) | Stores the results of a query in a temporary table. | CREATE TEMPORARY TABLE name AS query; | +| [CREATE VIEW]({{site.baseurl}}/docs/create-view) | Creates a virtual structure for the result set of a stored query.- | CREATE [OR REPLACE] VIEW [workspace.]view_name [ (column_name [, ...]) ] AS query; | +| [DESCRIBE ]({{site.baseurl}}/docs/describe) | Returns information about columns in a table or view. | DESCRIBE [workspace.]table_name | +| [DROP VIEW]({{site.baseurl}}/docs/drop-view) | Removes a view. | DROP VIEW [workspace.]view_name ; | +| [EXPLAIN PLAN FOR]({{site.baseurl}}/docs/explain) | Returns the physical plan for a particular query. | EXPLAIN PLAN FOR query; | +| [EXPLAIN PLAN WITHOUT IMPLEMENTATION FOR]({{site.baseurl}}/docs/explain) | Returns the logical plan for a particular query. | EXPLAIN PLAN WITHOUT IMPLEMENTATION FOR query; | +| [RESET]({{site.baseurl}}/docs/reset) | Changes a session setting back to its default system setting. For a list of Drill options and their descriptions, see [Planning and Execution Options]{{site.baseurl/docs/planning-and-execution-options/}}. | [ALTER SESSION] RESET \`option_name`; | +| [SELECT]({{site.baseurl}}/docs/select) | Retrieves data from tables and files. | [WITH subquery]SELECT column_list FROM table_name[WHERE clause] [GROUP BY clause][HAVING clause][ORDER BY clause]; | +| [SET]({{site.baseurl}}/docs/set) | Changes a system setting for the duration of a session. A session ends when you quit the Drill shell. For a list of Drill options and their descriptions, see [Planning and Execution Options]{{site.baseurl/docs/planning-and-execution-options/}}. | [ALTER SESSION] SET \`option_name` = value; | +| [SHOW DATABASES]({{site.baseurl}}/docs/show-databases-and-show-schemas) | Returns a list of available schemas. Equivalent to SHOW SCHEMAS. | SHOW DATABASES; | +| [SHOW FILES]({{site.baseurl}}/docs/show-files) | Returns a list of files in a file system schema. | SHOW FILES IN|FROM filesystem.\`schema_name`; | +| [SHOW SCHEMAS]({{site.baseurl}}/docs/show-databases-and-show-schemas) | Returns a list of available schemas. Equivalent to SHOW DATABASES. | SHOW SCHEMAS; | +| [SHOW TABLES]({{site.baseurl}}/docs/show-tables) | Returns a list of tables and views. | SHOW TABLES; | +| [USE]({{site.baseurl}}/docs/use) | Change to a particular schema. When you opt to use a particular schema, Drill issues queries on that schema only. | USE schema_name; | [...] diff --git a/_docs/sql-reference/sql-commands/007-analyze-table-refresh-metadata.md b/_docs/sql-reference/sql-commands/007-analyze-table-refresh-metadata.md index 21fa711..50ad00e 100644 --- a/_docs/sql-reference/sql-commands/007-analyze-table-refresh-metadata.md +++ b/_docs/sql-reference/sql-commands/007-analyze-table-refresh-metadata.md @@ -1,158 +1,112 @@ --- title: "ANALYZE TABLE REFRESH METADATA" parent: "SQL Commands" -date: 2020-01-13 +date: 2020-01-30 --- Starting from Drill 1.17, you can store table metadata (including schema and computed statistics) into Drill Metastore. This metadata will be used when querying a table for more optimal plan creation. +The Metastore is an Alpha feature; it is subject to change. We encourage you to try it and provide feedback. +Because the Metastore is in Beta, the SQL commands and Metastore formats may change in the next release. {% include startnote.html %}In Drill 1.17, this feature is supported for Parquet tables only and is disabled by default.{% include endnote.html %} -To enable Drill Metastore usage, the following option `metastore.enabled` should be set to `true`, as shown: +To use the Drill Metastore, you must enable it at the session or system level with one of the following commands: SET `metastore.enabled` = true; + ALTER SYSTEM SET `metastore.enabled` = true; Alternatively, you can enable the option in the Drill Web UI at `http://<drill-hostname-or-ip-address>:8047/options`. +Once you enable the Metastore, the next step is to populate it with data. Drill can query a table whether that table + has a Metastore entry or not. (If you are familiar with Hive, then you know that Hive requires that all tables have + Hive Metastore entries before you can query them.) In Drill, only add data to the Metastore when doing so improves + query performance. In general, large tables benefit from statistics more than small tables do. + +Unlike Hive, Drill does not require you to declare a schema. Instead, Drill infers the schema by scanning your table + and computes some metadata like MIN / MAX column values and NULLS COUNT designated as "metadata" to be able to + produce more optimizations like filter push-down, etc. If `planner.statistics.use` option is enabled, this command + will also calculate and store table statistics into Drill Metastore. + +Unlike Hive, Drill does not require you to declare a schema. Instead, Drill infers the schema by scanning your table + in the same way as it is done during regular select. + ## Syntax The ANALYZE TABLE REFRESH METADATA statement supports the following syntax: ANALYZE TABLE [table_name] [COLUMNS {(col1, col2, ...) | NONE}] REFRESH METADATA ['level' LEVEL] - [{COMPUTE | ESTIMATE} | STATISTICS [(column1, column2, ...)] + [{COMPUTE | ESTIMATE} | STATISTICS [ SAMPLE number PERCENT ]] ## Parameters *table_name* -The name of the table or directory for which Drill will collect table metadata. If the table does not exist, or the table - is temporary, the command fails and metadata is not collected and stored. +The name of the table or directory for which Drill will collect table metadata. If the table does not exist, the table + is temporary or if you do not have permission to read the table, the command fails and metadata is not collected and stored. *COLUMNS (col1, col2, ...)* -Optional names of the column(s) for which Drill will generate and store metadata. the Stored schema will include all table columns. +Optional names of the column(s) for which Drill will compute and store statistics. The stored schema will include all + table columns. *COLUMNS NONE* -Specifies to ignore collecting and storing metadata for all table columns. +Drill will infer schema for all columns, but gather statistics for none of the columns. *level* -Optional varchar literal which specifies maximum level depth for collecting metadata. -Possible values: `TABLE`, `SEGMENT`, `PARTITION`, `FILE`, `ROW_GROUP`, `ALL`. Default is `ALL`. +Optional VARCHAR literal which specifies maximum level depth for collecting metadata. +Possible values: + +- `TABLE` - metadata will be collected at table level (MIN / MAX column values within whole the table, etc.); +- `SEGMENT` - metadata will be collected for every segment within the table (MIN / MAX column values within a specific + segment) + metadata at table level. Segment term here is used to abstract data part which may correspond to a + specific directory on the file system or partition in Hive table; +- `PARTITION` - metadata will be collected for every partition within the table (MIN / MAX column values within a + specific partition) + file, segment and table metadata. Partition term is used here to abstract part of the data + where some column(s) have the same values (corresponds to existing Drill partitions for Parquet table). Is not + implemented in Drill 1.17; +- `FILE` - metadata will be collected for every file within the table (MIN / MAX column values within a specific file) + + partition, segment metadata and table metadata; +- `ROW_GROUP` - metadata will be collected for every row group within the table (MIN / MAX column values within a + specific row group) + file, partition, segment metadata and table metadata. Supported for Parquet tables only; +- `ALL` - metadata will be collected for every splittable table part - row groups for parquet, files for regular file + storage formats, etc. +Default is `ALL`. *COMPUTE* -Generates statistics for the table to be stored into the Metastore. -If statistics usage is disabled (`planner.enable_statistics` is set to `false`), an error will be thrown when this clause is specified. +Computes statistics for the table to be stored into the Metastore. +If statistics usage is disabled (`planner.statistics.use` is set to `false`), an error will be thrown when this clause is specified. *ESTIMATE* -Generates estimated statistics for the table to be stored into the Metastore. Currently is not supported. +Computes estimated statistics for the table to be stored into the Metastore. Currently is not supported. *(column1, column2, ...)* -The name of the column(s) for which Drill will generate statistics. +The name of the column(s) for which Drill will compute statistics. *SAMPLE* Optional. Indicates that compute statistics should run on a subset of the data. *number PERCENT* -An integer that specifies the percentage of data on which to compute statistics. For example, if a table has 100 rows, `SAMPLE 50 PERCENT` indicates that statistics should be computed on 50 rows. The optimizer selects the rows at random. - -## Related Options - -- **metastore.enabled** -Enables Drill Metastore usage to be able to store table metadata during ANALYZE TABLE commands execution and to be able - to read table metadata during regular queries execution or when querying some INFORMATION_SCHEMA tables. Default is `false`. -- **metastore.metadata.store.depth_level** -Specifies maximum level depth for collecting metadata. Default is `'ALL'`. -- **metastore.retrieval.retry_attempts** -Specifies the number of attempts for retrying query planning after detecting that query metadata is changed. -If the number of retries was exceeded, query will be planned without metadata information from the Metastore. Default is 5. -- **metastore.metadata.fallback_to_file_metadata** -Allows using file metadata cache for the case when required metadata is absent in the Metastore. Default is true. -- **metastore.metadata.use_schema** -Enables schema usage, stored to the Metastore. Default is `true`. -- **metastore.metadata.use_statistics** -Enables statistics usage, stored in the Metastore, at the planning stage. Default is `true`. -- **metastore.metadata.ctas.auto-collect** -Specifies whether schema and / or column statistics will be automatically collected for every table after CTAS and CTTAS. -This option is not active for now. Default is `'NONE'`. -- **drill.exec.storage.implicit.last_modified_time.column.label** -Sets the implicit column name for the last modified time (`lmt`) column. For internal usage when producing Metastore analyze. -- **drill.exec.storage.implicit.row_group_index.column.label** -Sets the implicit column name for the row group index (`rgi`) column. For internal usage when producing Metastore analyze. -- **drill.exec.storage.implicit.row_group_length.column.label** -Sets the implicit column name for the row group length (`rgl`) column. For internal usage when producing Metastore analyze. -- **drill.exec.storage.implicit.row_group_start.column.label** -Sets the implicit column name for the row group start (`rgs`) column. For internal usage when producing Metastore analyze. +An integer that specifies the percentage of data on which to compute statistics. For example, if a table has 100 rows, + `SAMPLE 50 PERCENT` indicates that statistics should be computed on 50 rows. The optimizer selects the rows at random. ## Related Commands -To drop table metadata from the Metastore, the following command may be used: +Use the following command to remove a table from the Metastore: ANALYZE TABLE [table_name] DROP [METADATA|STATISTICS] [IF EXISTS] -It will not throw an exception for absent table metadata if `IF EXISTS` clause was specified. - - ANALYZE TABLE [workspace.]table_name COMPUTE STATISTICS [(column1, column2,...)] [SAMPLE number PERCENT] - -See [ANALYZE TABLE COMPUTE STATISTICS]({{site.baseurl}}/docs/analyze-table-compute-statistics). - -## Usage Notes - -### General Information - -- Currently `ANALYZE TABLE REFRESH METADATA` statement can compute and store metadata only for Parquet tables within `dfs` storage plugins. -- For the case when `ANALYZE TABLE REFRESH METADATA` command is executed for the first time, whole table metadata will be collected and stored into Metastore. -If analyze was already executed for the table, and table data wasn't changed, all further analyze commands wouldn't trigger table analyzing and message that table metadata is up to date will be returned. - -### Incremental analyze +The command will fail if the table does not exist in the Metastore. Include the `IF EXISTS` clause to ignore a missing table. -For the case when some table data was updated, Drill will try to execute incremental analyze - calculate metadata only for updated data and reuse required metadata from the Metastore. + ANALYZE TABLE [plugin.schema.]table_name COMPUTE STATISTICS [(column1, column2,...)] [SAMPLE number PERCENT] -Incremental analyze wouldn't be produced for the following cases: -- list of interesting columns specified in analyze is not a subset of interesting columns from the previous analyze; -- specified metadata level differs from the metadata level in previous analyze. +See [ANALYZE TABLE COMPUTE STATISTICS]({{site.baseurl}}/docs/analyze-table-compute-statistics). -### Metadata usage + REFRESH TABLE METADATA [ COLUMNS ( column1, column2...) | NONE ] table_path -Drill provides the ability to use metadata obtained from the Metastore at the planning stage to prune segments, files - and row groups. +For the case when table metadata was stored into the Drill Metastore, Parquet table metadata cache files, wouldn't be + used for the same table during query execution if all required metadata is present and is not outdated. -Tables metadata from the Metastore is exposed to `INFORMATION_SCHEMA` tables (if Metastore usage is enabled). - -The following tables are populated with table metadata from the Metastore: - -`TABLES` table has the following additional columns populated from the Metastore: - -- `TABLE_SOURCE` - table data type: `PARQUET`, `CSV`, `JSON` -- `LOCATION` - table location: `/tmp/nation` -- `NUM_ROWS` - number of rows in a table if known, `null` if not known -- `LAST_MODIFIED_TIME` - table's last modification time - -`COLUMNS` table has the following additional columns populated from the Metastore: - -- `COLUMN_DEFAULT` - column default value -- `COLUMN_FORMAT` - usually applicable for date time columns: `yyyy-MM-dd` -- `NUM_NULLS` - number of nulls in column values -- `MIN_VAL` - column min value in String representation: `aaa` -- `MAX_VAL` - column max value in String representation: `zzz` -- `NDV` - number of distinct values in column, expressed in Double -- `EST_NUM_NON_NULLS` - estimated number of non null values, expressed in Double -- `IS_NESTED` - if column is nested. Nested columns are extracted from columns with struct type. - -`PARTITIONS` table has the following additional columns populated from the Metastore: - -- `TABLE_CATALOG` - table catalog (currently we have only one catalog): `DRILL` -- `TABLE_SCHEMA` - table schema: `dfs.tmp` -- `TABLE_NAME` - table name: `nation` -- `METADATA_KEY` - top level segment key, the same for all nested segments and partitions: `part_int=3` -- `METADATA_TYPE` - `SEGMENT` or `PARTITION` -- `METADATA_IDENTIFIER` - current metadata identifier: `part_int=3/part_varchar=g` -- `PARTITION_COLUMN` - partition column name: `part_varchar` -- `PARTITION_VALUE` - partition column value: `g` -- `LOCATION` - segment location, `null` for partitions: `/tmp/nation/part_int=3` -- `LAST_MODIFIED_TIME` - last modification time - -## Limitations +## Usage Notes -This feature is currently in the alpha phase (preview, experimental) for Drill 1.17 and only applies to Parquet - tables in this release. You must enable this feature through the `metastore.enabled` system/session option. +For more detailed information on Drill Metastore and its usage please refer to [Using Drill Metastore]({{site.baseurl}}/docs/using-drill-metastore).
