Table SQL Statements
Tables can be created in either the shared $scratch
location or in filesystem sources types.
In addition, if tables are created in a source, they can also be dropped.
Creating Tables
The CREATE TABLE AS
command is used to create tables.
Tables are created in either the shared $scratch
location or in filesystem sources types.
[info] Tables are written in Parquet format and can be used with
HASH PARTITION
andLOCALSORT
options.
$scratch Location
By default, the $scratch
location points to /scratch
directory under Dremio's
configured distributed cache location (paths.dist
in dremio.conf
).
There are no security protocols or permissions associated with this location -- it is readable and writable by all users.
Filesystem Sources
Dremio CTAS supports all filesystem source types (S3, ADLS, NAS, HDFS, and MapR-FS) using the filesystem permissions for the written table using impersonation.
Enabling CTAS and DROP TABLE for Sources
In order to use the CREATE TABLE AS
(CTAS) and DROP TABLE
(DROP) commands on a source,
you must enable exports for the source.
If enabled, all users/groups who have access to the source can leverage CTAS and DROP TABLE on sources.
Exports can be enabled:
- When creating a new source.
- By editing an existing source.
To enable exports into the source:
- Select
Advanced Options
. - Check the
Enable exports into this source (CTAS and DROP)
box.
[info] CTAS functionality is enabled on a per source basis.
Impersonation
For sources that support impersonation (HDFS and MapR File System) and if impersonation is enabled,
Dremio executes the CTAS and DROP TABLE operations as the user running the command.
This requires that the user have the appropriate permissions (read, execute, and/or write) for the operation.
If impersonation is not enabled, the operation is executed as the user running the Dremio service user.
[info]
For additional security, access can be further controlled by selecting specifically restricted filesystem sub-directories when you initially create your Dremio source.
Limitations include:
- Virtual datasets can be exported to their respective sources via standard SQL-based commands ONLY.
- DROP TABLE functionality on S3 or ADLS source datasets is slow when working with very large tables.
Syntax
$scratch location syntax
CREATE TABLE $scratch.<TABLE_NAME>
[HASH PARTITION BY (column, . .) ]
[LOCALSORT BY (column) ]
AS <QUERY>
Filesystem source syntax
CREATE TABLE <source>.<TABLE_NAME>
[HASH PARTITION BY (column, . .) ]
[LOCALSORT BY (column) ]
AS <QUERY>
Example
Creating $scratch table
CREATE TABLE $scratch.my_table
AS select * from TPCH.lineitem
Creating source table
CREATE TABLE s3.upload.lineitem2
AS select * from TPCH.lineitem
Once a source table has been created, the Dremio UI displays the following:
- Path where the table was created.
- Number of records in the new table.
- File size of the table.
Querying Tables
Querying $scratch table
select count(*) from $scratch.my_table
Querying source table
select count(*) from s3.upload.lineitem2
Dropping Tables
Tables can be dropped only if they have been created on a filesystem source and if you have permission.
Syntax
DROP TABLE <source>.<TABLE_NAME>
Example
DROP TABLE s3.upload.lineitem2