Querying System Tables
Drill has a sys database that contains system tables. You can query the system tables for information about Drill, including Drill ports, the Drill version running on the system, and available Drill options. View the databases in Drill to identify the sys database, and then use the sys database to view system tables that you can query.
View Drill Databases
Issue the SHOW DATABASES command to view Drill databases.
0: jdbc:drill:zk=10.10.100.113:5181> show databases;
+--------------------+
| SCHEMA_NAME |
+--------------------+
| M7 |
| hive.default |
| dfs.default |
| dfs.root |
| dfs.views |
| dfs.tmp |
| dfs.tpcds |
| sys |
| cp.default |
| hbase |
| INFORMATION_SCHEMA |
+--------------------+
11 rows selected (0.162 seconds)
Drill returns sys in the database results.
Use the Sys Database
Issue the USE command to select the sys database for subsequent SQL
requests.
0: jdbc:drill:zk=10.10.100.113:5181> use sys;
+-------+----------------------------------+
| ok | summary |
+-------+----------------------------------+
| true | Default schema changed to [sys] |
+-------+----------------------------------+
1 row selected (0.101 seconds)
View Tables
Issue the SHOW TABLES command to view the tables in the sys database.
0: jdbc:drill:zk=10.10.100.113:5181> show tables;
+---------------+-------------+
| TABLE_SCHEMA | TABLE_NAME |
+---------------+-------------+
| sys | boot |
| sys | drillbits |
| sys | memory |
| sys | options |
| sys | threads |
| sys | version |
+---------------+-------------+
3 rows selected (0.934 seconds)
0: jdbc:drill:zk=10.10.100.113:5181>
Query System Tables
Query the drillbits, version, options, boot, threads, and memory tables in the sys database.
Query the drillbits table.
0: jdbc:drill:zk=10.10.100.113:5181> select * from drillbits;
+-------------------+------------+--------------+------------+---------+
| hostname | user_port | control_port | data_port | current|
+-------------------+------------+--------------+------------+--------+
| qa-node115.qa.lab | 31010 | 31011 | 31012 | true |
| qa-node114.qa.lab | 31010 | 31011 | 31012 | false |
| qa-node116.qa.lab | 31010 | 31011 | 31012 | false |
+-------------------+------------+--------------+------------+---------+
3 rows selected (0.146 seconds)
- hostname
The name of the node running the Drillbit service. - user_port
The user port address, used between nodes in a cluster for connecting to external clients and for the Drill Web Console. - control_port
The control port address, used between nodes for multi-node installation of Apache Drill. - data_port
The data port address, used between nodes for multi-node installation of Apache Drill. - current
True means the Drillbit is connected to the session or client running the query. This Drillbit is the Foreman for the current session.
Query the version table.
0: jdbc:drill:zk=10.10.100.113:5181> select * from version;
+-------------------------------------------+--------------------------------------------------------------------+----------------------------+--------------+----------------------------+
| commit_id | commit_message | commit_time | build_email | build_time |
+-------------------------------------------+--------------------------------------------------------------------+----------------------------+--------------+----------------------------+
| d8b19759657698581cc0d01d7038797952888123 | DRILL-3100: TestImpersonationDisabledWithMiniDFS fails on Windows | 15.05.2015 @ 05:18:03 UTC | Unknown | 15.05.2015 @ 06:52:32 UTC |
+-------------------------------------------+--------------------------------------------------------------------+----------------------------+--------------+----------------------------+
1 row selected (0.099 seconds)
- commit_id
The github id of the release you are running. For example, - commit_message
The message explaining the change. - commit_time
The date and time of the change. - build_email
The email address of the person who made the change, which is unknown in this example. - build_time
The time that the release was built.
Query the options table.
Drill provides system, session, and boot options that you can query.
The following example shows a query on the system options:
0: jdbc:drill:zk=10.10.100.113:5181> select * from options where type='SYSTEM' limit 10;
+-------------------------------------------------+----------+---------+----------+-------------+-------------+-----------+------------+
| name | kind | type | status | num_val | string_val | bool_val | float_val |
+-------------------------------------------------+----------+---------+----------+-------------+-------------+-----------+------------+
| drill.exec.functions.cast_empty_string_to_null | BOOLEAN | SYSTEM | DEFAULT | null | null | false | null |
| drill.exec.storage.file.partition.column.label | STRING | SYSTEM | DEFAULT | null | dir | null | null |
| exec.errors.verbose | BOOLEAN | SYSTEM | DEFAULT | null | null | false | null |
| exec.java_compiler | STRING | SYSTEM | DEFAULT | null | DEFAULT | null | null |
| exec.java_compiler_debug | BOOLEAN | SYSTEM | DEFAULT | null | null | true | null |
| exec.java_compiler_janino_maxsize | LONG | SYSTEM | DEFAULT | 262144 | null | null | null |
| exec.max_hash_table_size | LONG | SYSTEM | DEFAULT | 1073741824 | null | null | null |
| exec.min_hash_table_size | LONG | SYSTEM | DEFAULT | 65536 | null | null | null |
| exec.queue.enable | BOOLEAN | SYSTEM | DEFAULT | null | null | false | null |
| exec.queue.large | LONG | SYSTEM | DEFAULT | 10 | null | null | null |
+-------------------------------------------------+----------+---------+----------+-------------+-------------+-----------+------------+
10 rows selected (0.216 seconds)
- name
The name of the option. - kind
The data type of the option value. - type
The type of options in the output: system or session. - status The status of the option: default or changed.
- num_val
The default value, which is of the long or int data type; otherwise, null. - string_val
The default value, which is a string; otherwise, null. - bool_val
The default value, which is true or false; otherwise, null. - float_val
The default value, which is of the double, float, or long double data type; otherwise, null.
Query the boot table.
0: jdbc:drill:zk=10.10.100.113:5181> select * from boot limit 10;
+--------------------------------------+----------+-------+---------+------------+-------------------------+-----------+------------+
| name | kind | type | status | num_val | string_val | bool_val | float_val |
+--------------------------------------+----------+-------+---------+------------+-------------------------+-----------+------------+
| awt.toolkit | STRING | BOOT | BOOT | null | "sun.awt.X11.XToolkit" | null | null |
| drill.client.supports-complex-types | BOOLEAN | BOOT | BOOT | null | null | true | null |
| drill.exec.buffer.size | STRING | BOOT | BOOT | null | "6" | null | null |
| drill.exec.buffer.spooling.delete | BOOLEAN | BOOT | BOOT | null | null | true | null |
| drill.exec.buffer.spooling.size | LONG | BOOT | BOOT | 100000000 | null | null | null |
| drill.exec.cluster-id | STRING | BOOT | BOOT | null | "SKCluster" | null | null |
| drill.exec.compile.cache_max_size | LONG | BOOT | BOOT | 1000 | null | null | null |
| drill.exec.compile.compiler | STRING | BOOT | BOOT | null | "DEFAULT" | null | null |
| drill.exec.compile.debug | BOOLEAN | BOOT | BOOT | null | null | true | null |
| drill.exec.compile.janino_maxsize | LONG | BOOT | BOOT | 262144 | null | null | null |
+--------------------------------------+----------+-------+---------+------------+-------------------------+-----------+------------+
10 rows selected (0.192 seconds)
- name
The name of the boot option. - kind
The data type of the option value. - type
This is always boot. - status This is always boot.
- num_val
The default value, which is of the long or int data type; otherwise, null. - string_val
The default value, which is a string; otherwise, null. - bool_val
The default value, which is true or false; otherwise, null. - float_val
The default value, which is of the double, float, or long double data type; otherwise, null.
Query the threads table.
0: jdbc:drill:zk=10.10.100.113:5181> select * from threads;
+--------------------+------------+----------------+---------------+
| hostname | user_port | total_threads | busy_threads |
+--------------------+------------+----------------+---------------+
| qa-node115.qa.lab | 31010 | 33 | 33 |
| qa-node114.qa.lab | 31010 | 33 | 32 |
| qa-node116.qa.lab | 31010 | 29 | 29 |
+--------------------+------------+----------------+---------------+
3 rows selected (0.618 seconds)
- hostname
The name of the node running the Drillbit service. - user_port
The user port address, used between nodes in a cluster for connecting to external clients and for the Drill Web Console. - total_threads The peak thread count on the node.
- busy_threads The current number of live threads (daemon and non-daemon) on the node.
Query the memory table.
0: jdbc:drill:zk=10.10.100.113:5181> select * from memory;
+--------------------+------------+---------------+-------------+-----------------+---------------------+-------------+
| hostname | user_port | heap_current | heap_max | direct_current | jvm_direct_current | direct_max |
+--------------------+------------+---------------+-------------+-----------------+---------------------+-------------+
| qa-node115.qa.lab | 31010 | 443549712 | 4294967296 | 11798941 | 167772974 | 8589934592 |
| qa-node114.qa.lab | 31010 | 149948432 | 4294967296 | 7750365 | 134218542 | 8589934592 |
| qa-node116.qa.lab | 31010 | 358612992 | 4294967296 | 7750365 | 83886894 | 8589934592 |
+--------------------+------------+---------------+-------------+-----------------+---------------------+-------------+
3 rows selected (0.172 seconds)
- hostname
The name of the node running the Drillbit service. - user_port
The user port address, used between nodes in a cluster for connecting to external clients and for the Drill Web Console. - heap_current The amount of memory being used on the heap, in bytes.
- heap_max The maximum amount of memory available on the heap, in bytes.
- direct_current The current direct memory being used by the allocator, in bytes.
- jvm_direct_current The current JVM direct memory allocation, in bytes.
- direct_max The maximum direct memory available to the allocator, in bytes.
For information about how to configure Drill system and session options, see Planning and Execution Options.
For information about how to configure Drill start-up options, see Start-Up Options.