Apache Drill provides functionality for creating persistent aliases for its tables and storage names. These aliases can be used in queries instead of specifying the original name.
This feature is enabled by default and can be disabled using exec.enable_aliases
system option.
Aliases can be either public or user-specific.
Public aliases are available for all users, but only admins can create, modify or delete them.
The following command may be used to create public table alias:
CREATE PUBLIC ALIAS tpch_lineitem FOR TABLE cp.`tpch/lineitem.parquet`
Public alias for storage can be created with the following command:
CREATE PUBLIC ALIAS classpath FOR STORAGE cp
Existing public table aliases can be replaced with the following command:
CREATE OR REPLACE PUBLIC ALIAS tpch_lineitem FOR TABLE cp.`tpch/nation.parquet`
It is possible to omit TABLE
keyword:
CREATE OR REPLACE PUBLIC ALIAS tpch_lineitem FOR cp.`tpch/nation.parquet`
The following command can be used to drop public table alias:
DROP PUBLIC ALIAS tpch_lineitem FOR TABLE
Query to drop all public table aliases is the following:
DROP ALL PUBLIC ALIASES FOR TABLE
User aliases are specific for the concrete users, so different users can have aliases with the same name but different values. User aliases have a greater priority than public aliases, but if no user alias is found, a public one will be used.
The following command may be used to create user table alias:
CREATE ALIAS tpch_lineitem FOR TABLE cp.`tpch/lineitem.parquet`
User alias for storage can be created with the following command:
CREATE ALIAS classpath FOR STORAGE cp
Drill admins can also create aliases for other users with the following command:
CREATE ALIAS tpch_lineitem FOR TABLE cp.`tpch/lineitem.parquet` AS USER 'user1'
Non-admin users also can use syntax above, but only with their username.
Commands for replacing or deleting user aliases similar to the public one:
CREATE OR REPLACE ALIAS tpch_lineitem FOR TABLE cp.`tpch/nation.parquet`; CREATE OR REPLACE ALIAS tpch_lineitem FOR TABLE cp.`tpch/nation.parquet` AS USER 'user1'; DROP ALIAS tpch_lineitem FOR TABLE; DROP ALIAS tpch_lineitem FOR TABLE AS USER 'user1'; DROP ALL ALIASES FOR TABLE; DROP ALL ALIASES FOR TABLE AS USER 'user1';
Table aliases can be queried using sys.table_aliases
system table:
SELECT * FROM sys.table_aliases
alias<VARCHAR(OPTIONAL)> | name<VARCHAR(OPTIONAL)> | user<VARCHAR(OPTIONAL)> | isPublic<BIT(REQUIRED)> |
---|---|---|---|
t1 | cp .tpch/lineitem.parquet | null | true |
t2 | cp .tpch/lineitem.parquet | null | true |
t3 | cp .tpch/lineitem.parquet | testUser2 | false |
t3 | cp .tpch/nation.parquet | testUser1 | false |
Storage aliases can be obtained from sys.storage_aliases
system table:
SELECT * FROM sys.storage_aliases
alias<VARCHAR(OPTIONAL)> | name<VARCHAR(OPTIONAL)> | user<VARCHAR(OPTIONAL)> | isPublic<BIT(REQUIRED)> |
---|---|---|---|
t1 | dfs | null | true |
t2 | cp | null | true |
t3 | dfs | testUser2 | false |
t3 | cp | testUser1 | false |
Table and storage aliases are stored in the pre-configured persistent store using storage_aliases
and table_aliases
accordingly.