This article explains the CREATE statements that can be used in Apache Impala in order to create databases, tables, views, functions and user roles.
Following are the examples.
Database:
Syntax:
CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name[COMMENT ‘database_comment’]
[LOCATION hdfs_path];
Example:
CREATE DATABASE IF NOT EXISTS TestDB COMMENT ‘This is a test database’
LOCATION ‘/user/cloudera/impalaworks’;
You can simply ignore the comments and location section.
CREATE DATABASE TestDB2;
Table:
Syntax:
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
(col_name data_type
[constraint_specification]
[COMMENT ‘col_comment’]
[, …]
)
[PARTITIONED BY (col_name data_type [COMMENT ‘col_comment’], …)]
[SORT BY ([column [, column …]])]
[COMMENT ‘table_comment’]
[ROW FORMAT row_format]
[WITH SERDEPROPERTIES (‘key1’=’value1’, ‘key2’=’value2’, …)]
[STORED AS file_format]
[LOCATION ‘hdfs_path’]
[CACHED IN ‘pool_name’ [WITH REPLICATION = integer] | UNCACHED]
[TBLPROPERTIES (‘key1’=’value1’, ‘key2’=’value2’, …)]
Examples:
CREATE EXTERNAL TABLE IF NOT EXISTS Emp(
empID Int,
Ename String,
Sal Decimal,
Dno Int)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ‘,’
LOCATION ‘/user/cloudera/empdirectory
TBLPROPERTIES(“skip.header.line.count”=”1”);
CREATE TABLE TestTable(Col1 INT, Col2 STRING);
View:
CREATE VIEW TestView
AS
SELECT Col1, Col2 FROM TestTable;
Function:
Creates a user-defined function (UDF), which you can use to implement custom logic during SELECT or INSERT operations.
Syntax to create a persistent scalar C++ UDF with CREATE FUNCTION:
CREATE FUNCTION [IF NOT EXISTS] [db_name.]function_name([arg_type[, arg_type…])
RETURNS return_type
LOCATION ‘hdfs_path_to_dot_so’
SYMBOL=’symbol_name’
Syntax to create a persistent Java UDF with CREATE FUNCTION:
CREATE FUNCTION [IF NOT EXISTS] [db_name.]function_name
LOCATION ‘hdfs_path_to_jar’
SYMBOL=’class_name’
Syntax to create a persistent UDA, which must be written in C++, issue a CREATE AGGREGATE FUNCTION statement:
CREATE [AGGREGATE] FUNCTION [IF NOT EXISTS] [db_name.]function_name([arg_type[, arg_type…])
RETURNS return_type
[INTERMEDIATE type_spec]
LOCATION ‘hdfs_path’
[INIT_FN=’function]
UPDATE_FN=’function
MERGE_FN=’function
[PREPARE_FN=’function]
[CLOSEFN=’function]
[SERIALIZE_FN=’function]
[FINALIZE_FN=’function]
Role:
The CREATE ROLE statement creates a role to which privileges can be granted. Privileges can be granted to roles, which can then be assigned to users. A user that has been assigned a role will only be able to exercise the privileges of that role. Only users that have administrative privileges can create/drop roles.
Only administrative users for Ranger can use this statement. Impala makes use of any roles and privileges specified by the GRANT and REVOKE statements in Hive, and Hive makes use of any roles and privileges specified by the GRANT and REVOKE statements in Impala.
Syntax:
CREATE ROLE role_name
Refer to the screenshots:
Hope you find this post useful.
Please subscribe for more interesting updates.
Big Data and SQL 
One comment