Apache Drill - Data Definition Statements

This section will cover Data definition statements. Let’s go through each of these commands in detail.

Create Statement

You can create tables in Apache Drill by using the following two CTAS commands.

Method 1

Syntax

CREATE TABLE name [ (column list) ] AS query;

Where,

Query − select statement.

Method 2

Syntax

CREATE TABLE name [ ( <column list> ) ] [ PARTITION BY ( <column_name> [ , ... ] ) ] AS <select statement>

Where,

  • name − unique directory name.

  • column list − optional list of column names or aliases in the new table.

  • PARTITION BY − partitions the data by the first column_name.

To create a table, you should adhere to the following steps −

  • Set the workspace to a writable workspace.

  • You can only create new tables in df.tmp workspace. You cannot create tables using storage plugins, such as Hive and HBase.

For example

"tmp": {
   "location": "/tmp",
   "writable": true,
}

Query

0: jdbc:drill:zk = local> use dfs.tmp;

Result

+-------+--------------------------------------+
|  ok   |               summary                |
+-------+--------------------------------------+
| true  |  Default schema changed to [dfs.tmp] |
+-------+--------------------------------------+

Query

0: jdbc:drill:zk = local> create table students as select * from
   dfs.`/Users/../workspace/Drill-samples/student_list.json`;

Result

+-----------+----------------------------+
| Fragment  |  Number of records written |
+-----------+----------------------------+
|    0_0    |            10              |
+-----------+----------------------------+

To view records

Query

0: jdbc:drill:zk = local> select * from students;

Result

+-----+-------+-----+--------+----------+-------+-------+-------+-------------------+---------+
| ID  | name  | age | gender | standard | mark1 | mark2 | mark3 |       addr        | pincode |
+-----+-------+-----+--------+----------+-------+-------+-------+-------------------+---------+
| 001 | Adam  | 12  |  male  |   six    |  70   |  50   |  60   | 23 new street     | 111222  |
| 002 | Amit  | 12  |  male  |   six    |  40   |  50   |  40   | 12 old street     | 111222  |
| 003 | Bob   | 12  |  male  |   six    |  60   |  80   |  70   | 10 cross street   | 111222  |
| 004 | David | 12  |  male  |   six    |  50   |  70   |  70   | 15 express avenue | 111222  |
| 005 | Esha  | 12  | female |   six    |  70   |  60   |  65   | 20 garden street  | 111222  |
| 006 | Ganga | 12  | female |   six    |  100  |  95   |  98   | 25 north street   | 111222  |
| 007 | Jack  | 13  |  male  |   six    |  55   |  45   |  45   | 2 park street     | 111222  |
| 008 | Leena | 12  | female |   six    |  90   |  85   |  95   | 24 south street   | 111222  |
| 009 | Mary  | 13  | female |   six    |  75   |  85   |  90   | 5 west street     | 111222  |
| 010 | Peter | 13  | female |   six    |  80   |  85   |  88   | 16 park avenue    | 111222  |
+-----+-------+-----+--------+----------+-------+-------+-------+-------------------+---------+

The following program shows the query for this function −

Query

0: jdbc:drill:zk = local> create table student_new partition by (gender) as select * from
   dfs.`/Users/../workspace/Drill-samples/student_list.json`;

Result

+-----------+----------------------------+
| Fragment  |  Number of records written |
+-----------+----------------------------+
|    0_0    |             10             |
+-----------+----------------------------+

To view the records of the table −

Query

0: jdbc:drill:zk = local> select * from student_new;

Result

+------+--------+------+--------+----------+-------+-------+-------+-------------------+---------+
| ID   |  name  | age  | gender | standard | mark1 | mark2 | mark3 |       addr        | pincode |
+------+--------+------+--------+----------+-------+-------+-------+-------------------+---------+
| 005  | Esha   | 12   | female |   six    |  70   |  60   |  65   | 20 garden street  | 111222  |
| 006  | Ganga  | 12   | female |   six    |  100  |  95   |  98   | 25 north street   | 111222  |
| 008  | Leena  | 12   | female |   six    |  90   |  85   |  95   | 24 south street   | 111222  |
| 009  | Mary   | 13   | female |   six    |  75   |  85   |  90   | 5 west street     | 111222  |
| 010  | Peter  | 13   | female |   six    |  80   |  85   |  88   | 16 park avenue    | 111222  |
| 001  | Adam   | 12   |  male  |   six    |  70   |  50   |  60   | 23 new street     | 111222  |
| 002  | Amit   | 12   |  male  |   six    |  40   |  50   |  40   | 12 old street     | 111222  |
| 003  | Bob    | 12   |  male  |   six    |  60   |  80   |  70   | 10 cross street   | 111222  |
| 004  | David  | 12   |  male  |   six    |  50   |  70   |  70   | 15 express avenue | 111222  |
| 007  | Jack   | 13   |  male  |   six    |  55   |  45   |  45   | 2 park street     | 111222  |
+------+--------+------+--------+----------+-------+-------+-------+-------------------+---------+

Here the table records are partitioned by gender.

Alter Statement

The ALTER SYSTEM command permanently changes a system setting.

Syntax

ALTER SYSTEM SET `option_name` = value;

To reset the system settings, use the following syntax.

ALTER SYSTEM RESET `option_name`;
ALTER SYSTEM RESET ALL;

Query

Here is the sample query that enables the Decimal data type −

0: jdbc:drill:zk = local> ALTER SYSTEM SET `planner.enable_decimal_data_type` = true;

Result

+-------+--------------------------------------------+
|  ok   |                  summary                   |
+-------+--------------------------------------------+
| true  |  planner.enable_decimal_data_type updated. |
+-------+--------------------------------------------+

By default, Apache Drill disables the decimal data type. To reset all the changes, you will need to key-in the following command −

Query

0: jdbc:drill:zk = local> ALTER SYSTEM RESET all;

Result

+-------+---------------+
|  ok   |    summary    |
+-------+---------------+
| true  | ALL updated.  |
+-------+---------------+

Create View Statement

The CREATE VIEW command creates a virtual structure for the result set of a stored query. A view can combine data from multiple underlying data sources and provide the illusion that all of the data is from one source.

Syntax

CREATE [OR REPLACE] VIEW [workspace.]view_name [ (column_name [, ...]) ] AS query;

Where,

  • workspace − The location where you want the view to exist. By default, the view can be created in “dfs.tmp”.

  • view_name − The name that you give to the view. This view must have a unique name.

Query

0: jdbc:drill:zk = local> create view student_view as select * from
   dfs.`/Users/../workspace/Drill-samples/student_list.json`;

Result

+------+--------------------------------------------------------------+
|  ok  |                        summary                               |
+------+--------------------------------------------------------------+
| true | View 'student_view' created successfully in 'dfs.tmp' schema |
+------+--------------------------------------------------------------+

To see the records, you can use the following query.

Query

0: jdbc:drill:zk = local> select * from student_view;

Result

+-----+-------+-----+--------+----------+-------+-------+-------+-------------------+---------+
| ID  | name  | age | gender | standard | mark1 | mark2 | mark3 |        addr       | pincode |
+-----+-------+-----+--------+----------+-------+-------+-------+-------------------+---------+
| 001 | Adam  | 12  |  male  |   six    |  70   |  50   |  60   | 23 new street     | 111222  |
| 002 | Amit  | 12  |  male  |   six    |  40   |  50   |  40   | 12 old street     | 111222  |
| 003 | Bob   | 12  |  male  |   six    |  60   |  80   |  70   | 10 cross street   | 111222  |
| 004 | David | 12  |  male  |   six    |  50   |  70   |  70   | 15 express avenue | 111222  |
| 005 | Esha  | 12  | female |   six    |  70   |  60   |  65   | 20 garden street  | 111222  |
| 006 | Ganga | 12  | female |   six    |  100  |  95   |  98   | 25 north street   | 111222  |
| 007 | Jack  | 13  |  male  |   six    |  55   |  45   |  45   | 2 park street     | 111222  |
| 008 | Leena | 12  | female |   six    |  90   |  85   |  95   | 24 south street   | 111222  |
| 009 | Mary  | 13  | female |   six    |  75   |  85   |  90   | 5 west street     | 111222  |
| 010 | Peter | 13  | female |   six    |  80   |  85   |  88   | 16 park avenue    | 111222  |
+-----+-------+-----+--------+----------+-------+-------+-------+-------------------+---------+

Drop Table

The drop table statement is used to drop the table from a DFS storage plugin.

Syntax

DROP TABLE [workspace.]name;

Query

0: jdbc:drill:zk = local> drop table student_new;

Result

+------+------------------------------+
|  ok  |          summary             |
+------+------------------------------+
| true | Table [student_new] dropped  |
+------+------------------------------+

Drop View

Similar to the table, a view can be dropped by using the following command −

Query

0: jdbc:drill:zk = local> drop view student_view;

Result

+------+-----------------------------------------------------------------+
|  ok  |                            summary                              |
+------+-----------------------------------------------------------------+
| true | View [student_view] deleted successfully from schema [dfs.tmp]. |
+------+-----------------------------------------------------------------+
Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements