Currently (starting from Drill version 1.8 and older) Drill supports only permanent tables [1] which are created using create table as select statement (hereinafter CTAS). They are created physically on disk and can be accessed by all users and persist after user session is closed. Data format stored in permanent tables can be regulated using store.format option. Permanent tables which store data in parquet format can be partitioned [2]. Permanent tables can be dropped using DROP command [3]. Permanent tables can be created only using CTAS statement and currently do not support INSERT statement.
Drill also supports views [4] which are created as files on disk with .view.drill extension. Such files contain select statement which can combine multiple sources. Access to views can be restricted using impersonalization [5]. As permanent tables views persist after user session is closed. Views can be dropped using DROP command [6].
Permanent tables and views can be created only in file based schema presented by file storage plugin and in writable workspace. If workspace and schema is not indicated during creation, they will be created in workspace user is currently in. Permanent table and view with the same name can not exists in the same workspace.
Many RDBMS support temporary tables which are very useful feature for storing user intermediate results. Unlike permanent tables and views, temporary tables can be only accessed within user session and are destroyed automatically when session is closed. Jira DRILL-4956 aims to add temporary tables support in Drill.
The API must provide SQL syntax to create and drop temporary tables. Temporary table MUST exist during user session only, when session is closed temporary table MUST be dropped automatically. Temporary tables with the same name can be created in different sessions. Only user who initiated the session MUST have access to temporary table.
User would like to store some intermediate results without creating permanent table or view. Also user wants to be sure such intermediate results can be accessed only by him. User wants to use such results in subsequent queries to improve performance (i.e. stop recalculating the same data over and over again). Also user wants to relieve the burden of dropping objects with such results once session is closed.
Many RDBMS (Oracle, DB2, SQLServer, MySql [7], Postgre [8]) and Apache Hive [9] support temporary tables. Temporary tables have the following common features:
- To create temporary table user needs to specify special keyword in CREATE statement syntax, like TEMPORARY.
- Temporary table can be accessed only by the user who initiated the session among this session.
- Temporary table exists within a session.
- Temporary table MUST be dropped automatically when session is closed.
- User can drop and recreate the same temporary table during session any number of times.
- Temporary tables with the same name can be created in different sessions.
Drill will use all these common features in its own temporary tables implementation.
Create temporary table as select (hereinafter CTTAS) will be used to create temporary table. Temporary tables can be created only in default temporary workspace. Temporary table will be accessible during the session only. If not dropped by the user during the session, it will be dropped automatically when session is closed. Temporary tables data will be stored physically on disk but only user who initiated the session will have access to such files directly (will be restricted by file system rights).
To access temporary table, user MUST specify name indicated in CTTAS. Users will be able to create several temporary tables with the same name in different sessions. Internally temporary tables will be stored in folder named after session id, temporary table name will be replaced to unique generated only to mask original temporary table name.
Configuration file drill-module.conf will contain the following default required for temporary tables support:
drill.exec.default_temporary_workspace: "dfs.tmp"
Please note workspace name is case-sensitive. Temporary tables can be created in default temporary workspace only. User may override this property in drill-override.conf to store temporary table in different workspace. During temporary table creation and drop passed workspace checked for compliance with the following:
- Workspace and its directory MUST exist.
- Workspace MUST be writable.
- Workspace MUST be file-based.
- Passed workspace MUST be the same as default temporary workspace indicated in configuration.
If at least one of the above is not true, drillbit start up will fail.
Temporary table creation will be triggered by the end user command. The command will have one option:
CREATE TEMPORARY TABLE - returns failure if:
- Permanent table, view or temporary table with the same name exists in indicated workspace or in default temporary workspace if workspace was not indicated.
- Workspace where temporary table is going to be created does not exist or is not writable.
CREATE statement has syntax errors, like target and source columns number mismatch, invalid columns in partition by clause etc.
If temporary table creation was successful user will receive the following output with number of fragments written and number of records:
+-------------+-----------------------------------+
| Fragment | Number of records written |
+-------------+-----------------------------------+
| 0_0 | 5 |
+-------------+-----------------------------------+
Temporary table can be created over empty dataset, i.e. contain no records.
Temporary table drop will be triggered by the end user command. The command will have one option:
DROP TABLE [IF EXISTS] - returns failure if table is not found.
If temporary table drop was successful user will receive the following output:
+-------+-----------------------------------------------------+
| ok | summary |
+-------+-----------------------------------------------------+
| true | Temporary table [my_table] was dropped. |
+-------+-----------------------------------------------------+
If IF EXISTS clause was specified and temporary table was not found, user will receive the following output:
+--------+------------------------------------------+
| ok | summary |
+--------+------------------------------------------+
| false | Table [my_table] was not found. |
+--------+------------------------------------------+
Data selection procedure from temporary table is the same as from regular table. Temporary tables can be created only in default temporary workspace. User MAY or MAY NOT indicated workspace in SELECT statement. Both SELECT statements below will return result from the same temporary table (if dfs.tmp is default temporary workspace):
select * from my_tempselect * from dfs.tmp.my_temp
CREATE (TEMPORARY) TABLE dest_name [ (column, . . .) ]
[ PARTITION BY (column, . . .) ]
AS SELECT column_list FROM <source_name>
Syntax is similar to permanent tables syntax, except TEMPORARY keyword is used. Currently Drill does not support INSERT statement, data in permanent or temporary table can be added only during creation.
When writing CTTAS user MUST comply with the following rules (the same rules apply for CTAS, except point 6):
- Indicating column list for destination table is optional but if indicated number of columns MUST be the same as number of columns in select statement.
- If column names are not indicated for destination table, columns names from select statement will be used. If column has alias, alias name will be used.
- Partition by clause is optional but if used MUST specify column or list of columns to be partitioned by.
- If destination columns list is indicated, column names used in partition by clause MUST be present in that list.
- If destination columns list is not indicated, column names used in partition by clause MUST be present in select statement.
- Adding schema and workspace to dest_name is optional, but if added workspace MUST be the same as default temporary workspace.
Temporary table is created with the name specified in CTTAS statement and can be access only by this name. Internally indicated temporary table will be replaced to unique generated name to mask original table name. Drill will internally resolve original temporary table name to generated one when table is accessed. All temporary tables that belong to the same session, will be stored in folder named after session id referred as session temporary location.
Temporary table can be created under default temporary workspace if:
- Permanent table or view with the same name is absent.
- Temporary table with the same name created under the same session is absent.
Temporary table names are case-insensitive. All below SELECT statements are valid:
select * from my_tempselect * from MY_TEMPselect * from mY_TeMp
If user issues CTTAS command without specifying workspace (create temporary table my_temp as select ...), table will be created in workspace specified in drill.exec.default.temporary.workspace option.
If workspace is specified in CTTAS, it should be the same as default temporary workspace, otherwise validation error will be thrown.
Temporary table can not be created outside of default temporary workspace. If user attempt to create temporary table outside of default temporary workspace, validation error will be thrown. Default temporary workspace itself MUST be writable and file-based.
Data in temporary tables can be stored in different formats, depending on store.format option setting. Option can be set at session level. Storage format can be set to parquet, json, csv etc.
Partition by clause is supported only for data stored in parquet format.
Temporary table is created physically on disk. All temporary tables that belong to the same session will be stored in separate folder named by session id. This folder is referred as session temporary location. This session temporary location is created when first CTTAS in session is issued. On session close this temporary location with its content (temporary tables folders and files that were not dropped by the user during session using DROP command) is deleted.
Please note, though default temporary workspace can not be changed during drillbit run, workspace location can be changed at runtime. If default temporary workspace location was changed at runtime, new session temporary location based on new workspace location will be created if CTTAS issued afterwards. On session close both session temporary locations will be deleted.
Temporary table original named is replaced to unique generated one to mask original table name. Temporary table is represented as folder named as temporary table unique generated name and files inside of this folder
Temporary tables can be created on local or distributed file system depending on schema configurations. If temporary table is created on a local file system, it can only be accessed by the local Drillbit that created it.
Columns names are set according to target column list specified during CTTAS. If column list was not provided, column names from source will be used. If source column names have alias, alias will be used.
Data stored in temporary table will have data types corresponding to source data types. Constants usage is allowed. If some source columns data types were transformed to different data type in source SELECT statement, transformed data types will be used.
Temporary table can be dropped the same way as permanent table using DROP TABLE command [3]:
DROP TABLE my_tempDROP TABLE dfs.wk1.my_temp
There is no need to indicate TEMPORARY keyword during temporary table drop. If indicated, it would cause invalid syntax error.
Before drop if workspace is not indicated and if indicated workspace is default temporary workspace, search for matching table will be conducted among temporary tables first. If temporary table is found, it will be dropped. If not, search will be conducted among persistent tables.
When temporary table is being dropped, it is deleted from the list of temporary tables associated with the session, temporary table folder will all its files are deleted from the file system.
Command will return an error if temporary table does not exist, unless IF EXISTS keywords are used (ex: DROP TABLE IF EXISTS my_temp). If IF EXISTS keywords are used, instead of error, user will receive appropriate output message.
When session is closed all existent temporary tables within this session will be dropped automatically. Since all temporary tables are located under session temporary location, only this location will be dropped (will all its child folders and files) on session close. Temporary tables created within different sessions, even under the same user, will not be dropped.
If drillbit goes down before user exits the session, all temporary location from opened sessions will be dropped.
Temporary table can be accessed only during session by name indicated in CTTAS command. The same user from different session can not access temporary table but can have temporary table with the same name.
If accessing temporary table by its generated name or directly by its files, only OS user who created temporary table can view its content, since temporary table folder is created with 700 permission and files inside of this folder with 600.
When authorization is enabled, all manipulations on file system are done under the user who started the drillbit. It means that temporary tables files will be created under the OS user who started the drillbit but not under the user who was authorized. This won’t affect accessing temporary tables by their names but if any user knows full path to temporary table generate name he can query them.
When impersonation is enabled, all manipulations on file system are done under the OS user who is logged in. In this case even if other user knows full path to temporary table or its files, he can not query them because temporary table folder is created with 700 permission, and its files are stored with 600 permissions (only OS user who created folder and files can access them). Only if the same OS user from different session knows full path to temporary folder or its files, he can query them.
After SELECT statement is issued by the user, this statement is being parsed and validated in Drill. During validation stage Drill by means of Apache Calcite checks if tables present in SELECT statement exist. Check if table from SELECT statement is temporary table will be done only if:
- Full table name includes workspace and this workspace is default temporary workspace.
- Workspace is not indicated near table name.
If temporary table is not found, Drill will conduct search as usual among persistent tables and views.
If user has persistent table or view in current user workspace and temporary table in default temporary workspace with the same name:
select * from my_tablewithout specifying schema will return data from temporary table in temporary default workspace.select * from dfs.wk1.my_tablewill return data from persistent table in dfs.wk1 workspace.
It’s not allowed to created views over temporary tables since there is no concept of temporary views which will be dropped on session close. Since views are not dropped on session close their definitions will be invalidated once temporary table are dropped.
If user will attempt to create view over temporary table the following validation error will be returned: Temporary tables usage is disallowed. Used temporary table name: [my_temp].
When temporary table is created or dropped by the user, appropriate message with be logged to drillbit.log:
- Temporary table [my_temp] is being created.
- Temporary table [my_temp] is being dropped.
If session temporary locations were dropped at the end of the session, appropriate message with be logged to drillbit.log:
- Deleted session temporary location [/tmp/xxx] from file system [file:///]"
User has csv file with the following data:
"1","Bob","200","false"
"2","Kate","150","true"
"3","Tim","20","false"
"4","Roger","500","true"
where first column is id, second - name, third - amount, fourth - discount.
User wants to create temporary table which will hold information about total amount of sold items with and without discount using the following SELECT statement:
create temporary table total_amount_by_discount as
select cast(columns[3] as boolean) discount, sum(cast(columns[2] as double)) total_amount from dfs.`amount.csv`
group by columns[3];
After statement is executed successfully user will receive the following output:
+-------------+-------------------------------------+
| Fragment | Number of records written |
+-------------+-------------------------------------+
| 0_0 | 2 |
+-------------+-------------------------------------+
User uses default storage format which is parquet. User did not specify workspace where to create temporary table, so default temporary workspace will be used which is dfs.tmp. Dfs.tmp location is /tmp.
After CTTAS command is successfully completed, new directory and file are created /tmp/b45c25f6-ae64-4be0-bdd2-a6e50d1d191d/249adc10-34d2-4259-8660-433818bba7dd/0_0_0.parquet, where:
b45c25f6-ae64-4be0-bdd2-a6e50d1d191d- session temporary location (folder named as session id)249adc10-34d2-4259-8660-433818bba7dd- unique generated temporary table name0_0_0.parquetis file with temporary table data
Directories will have 700 permission, file - 600.
drwx------. 2 user1 user1 49 Jan 18 13:29 b45c25f6-ae64-4be0-bdd2-a6e50d1d191d
drwx------. 2 user1 user1 51 Jan 18 13:29 249adc10-34d2-4259-8660-433818bba7dd
-rw-------. 1 user1 user1 1638 Jan 18 13:29 0_0_0.parquet
Using parquet-tools user can see that data was stored with transformed data types and column names:
java -jar parquet-tools-1.6.1-SNAPSHOT.jar schema /tmp/b45c25f6-ae64-4be0-bdd2-a6e50d1d191d/249adc10-34d2-4259-8660-433818bba7dd/0_0_0.parquet
message root {
optional boolean discount;
optional double total_amount;
}
select * from total_amount_by_discount will return the following:
+-----------+-------------------+
| discount | total_amount |
+-----------+-------------------+
| false | 220.0 |
| true | 650.0 |
+-----------+-------------------+
Each user session (org.apache.drill.exec.rpc.user.UserSession) is supplied with unique id (java.util.UUID.randomUUID()) upon creation. All temporary tables connected within this session will be created under the folder named by session id.
Temporary table name supplied by the user will be replaced with unique generated one (java.util.UUID.randomUUID()) to mask original table name. When temporary table is created, its original name and generated name is stored in session, so Drill can internally resolve temporary table name to generated one during access to temporary table.
If temporary table is dropped by the user, temporary table folder is dropped and removed from the list of temporary tables. To drop all temporary tables on session close, session temporary location with all its content will be dropped.
When table folder and files are created, permissions are set according to table type. For persistent tables 775 permissions will be set to folders, 644 - for files. For temporary tables 700 permissions will be set to folders, 600 - for files.
Adding temporary tables support adds extra effort during validation stage of all select statements. When Drill (using Calcite) validates table existence, if workspace is not specified or if workspace is default temporary workspace, Drill first searches for temporary table. If temporary table table is not found, search will be conducted as usual among persistent table and views. Preliminary performance tests did not show significant performance impact after adding above mentioned functionality.
During temporary table creation the following error messages can be returned:
- Duplicated table / view name -> A table or view with given name [my_table] already exists in schema [dfs.wk1].
- Not default temporary workspace -> Temporary tables are not allowed to be created outside of default temporary workspace [dfs.wk1].
- Absent workspace -> Schema [dfs.wk1] is not valid with respect to either root schema or current default schema. Current default schema: dfs.root.
- Not writable workspace -> Unable to create or drop tables/views. Schema [dfs.wk1] is immutable.
- Not file-based workspace -> Temporary workspace [dfs.wk1] must be file-based, instance of WorkspaceSchemaFactory.WorkspaceSchema.
- Non matching number of columns between target and source -> Table's field list and the table's query field list have different counts.
- Incorrect names for partition columns -> Partition column c1 is not in the SELECT list of CTAS!
- Partition by clause is not allowed for non parquet storage format ->Json format does not support auto-partitioning.
During temporary table drop the following error messages can be returned:
- Absent table -> Table [my_table] is not found.
- Absent workspace - Schema [dfs.wk1] is not valid with respect to either root schema or current default schema. Current default schema: dfs.root.
- Not writable workspace - Unable to create or drop tables/views. Schema [dfs.wk1] is immutable.
- Not file-based workspace -> Temporary workspace [dfs.wk1] must be file-based, instance of WorkspaceSchemaFactory.WorkspaceSchema.
Temporary workspace indicated in configuration file should be comply with the following rules:
- Workspace and its directory MUST exists.
- Workspace MUST be writable.
- Workspace MUST be file-based.
Please note, workspace name is case-sensitive.
NA
NA
NA
Since temporary tables support is a new feature in Drill 1.10.0, there are no backward compatibility issues.
Temporary tables can be accessed only within user session. Temporary table data is stored physically on disk, only user who created temporary table has access to temporary table folder. Folders are created with 700 permissions, files with 600. Refer to section 5.1.9 Access for more details.
NA
NA
NA
- Verify that temporary table is created using appropriate syntax.
- Verify that temporary table is not accessible from different session.
- Verify that temporary table can be dropped and recreated within the same session.
- Verify that temporary table is dropped when session is closed.
- Verify that temporary table can store data in different formats.
- Verify that temporary tables can be created only in default temporary workspace.
- Verify that temporary table is created in default temporary workspace if workspace is not indicated.
- Verify temporary table usage in different select statements.
- Verify that temporary table supports PARTITION BY clause.
- Verify that temporary table data is stored with appropriate permission: folder(-s) - 700, file(-s) - 600.
- Verify that default temporary workspace can be overridden in drill-override.conf.
- Verify that different drillbits can access temporary table data if temporary table is located on DFS.
- Verify temporary tables with authorization / impersonation enabled.
- Verify that views can not be created over temporary tables.
- Verify that temporary tables names are case-insensitive.
- Verify that if default temporary workspace location was changed at runtime (and in both locations temporary tables were created), all these temporary session locations will be dropped on session close.
Temporary tables can be created only using CTTAS statement. Currently Drill does not support adding data into tables using INSERT statement.
- Add CTTAS syntax support.
- Add temporary tables creation support.
- Add temporary tables custom / automatic drop.
- Add permissions during persistent and temporary tables creation.
- Add temporary tables priority during tables look up on planning stage.
- Add unit tests.
- [1] https://drill.apache.org/docs/create-table-as-ctas/
- [2] https://drill.apache.org/docs/partition-by-clause/
- [3] https://drill.apache.org/docs/drop-table/
- [4] https://drill.apache.org/docs/create-view/
- [5] https://drill.apache.org/docs/configuring-user-impersonation/
- [6] https://drill.apache.org/docs/drop-view/
- [7] http://dev.mysql.com/doc/refman/5.7/en/create-table.html
- [8] https://www.postgresql.org/docs/9.3/static/sql-createtable.html
- [9] https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-TemporaryTables
##10 Document History
| Date | Author | Version | Description |
|---|---|---|---|
| 2016-10-20 | Arina Ielchiieva | 0.1 | Initial Draft |