Specifies whether a Hive table is created and stored in the Hive warehouse or external to the Hive warehouse.
| Valid in: | SAS/ACCESS LIBNAME statement |
|---|---|
| Category: | Data Set Control |
| Aliases: | DBCREATE_EXTERNAL= |
| DBCREATE_EXT= | |
| Default: | NO |
| Interaction: | This option should be specified with the DBCREATE_TABLE_LOCATION= LIBNAME option. |
| Data source: | Hadoop |
| See: | DBCREATE_TABLE_LOCATION= LIBNAME option, DBCREATE_TABLE_OPTS= LIBNAME option, DBCREATE_TABLE_EXTERNAL= data set option, DBCREATE_TABLE_LOCATION= data set option, DBCREATE_TABLE_OPTS= data set option |
Table of Contents
creates an external table—one that is stored outside of the Hive warehouse.
creates a managed table—one that is managed within the Hive warehouse.
Specifying DBCREATE_TABLE_EXTERNAL=YES causes SAS to include the EXTERNAL keyword before the table name when creating a Hive table.
In this example, the combination of DBCREATE_TABLE_LOCATION='/tmp/extdir' and DBCREATE_TABLE_EXTERNAL=YES alters the CREATE TABLE query generated by the SAS DATA step.
libname x HADOOP user=xxxxxx pwd=xxxxxx
driverclass=com.cloudera.hive.jdbc.HS2Driver
DBCREATE_TABLE_EXTERNAL=yes
dbcreate_table_location='/tmp/extdir'
url='jdbc:hive2://host:port/database;useNativeQuery=1;defaultStringColumnLength=2048;
hive.exec.drop.ignorenonexistent=false';
data x.class;
set sashelp.class;
run;Here is an example of the log
HADOOP_6: Executed: on connection 2
CREATE EXTERNAL TABLE `default`.`CLASS` (`Name` VARCHAR(8),`Sex` VARCHAR(1),`Age` DOUBLE,`Height` DOUBLE,`Weight` DOUBLE)
LOCATION '/tmp/extdir/class' TBLPROPERTIES ('SAS OS Name'='Linux','SAS Version'='V.04.00M0D03312022')