Table action property

Use the Table action property to configure the connector to complete create, replace, and truncate actions on a table at run time. These actions are completed before any data is written to the table.

Usage

You can set the Table action property to the values that are listed in the following table.

Table 1. Values of the Table action property
Value Description
Append No action is completed on the table. This option is the default.
Create Create a table at run time.
Use one of these methods to specify the CREATE TABLE statement:
  • Set Generate create table statement at runtime to Yes and enter the name of the table to create in the Table name property. In this case, the connector automatically generates the CREATE TABLE statement from the column definitions on the input link. The column names in the new table match the column names on the link. The data types of columns in the new table are mapped to the column definitions on the link.
  • Set Generate create table statement at runtime to No, and enter the CREATE TABLE statement in the Create table statement property.
Replace Replace a table at run time.
Use one of these methods to specify the DROP TABLE statement:
  • Set Generate drop table statement at runtime to Yes, and enter the name of the table to drop in the Table name property.
  • Set Generate drop table statement at runtime to No, and enter the DROP TABLE statement in the Drop table statement property.
Use one of these methods to specify the CREATE TABLE statement:
  • Set Generate create table statement at runtime to Yes, and enter the name of the table to create in the Table name property.
  • Set Generate create table statement at runtime to No, and enter the CREATE TABLE statement in the Create table statement property.
Truncate Truncate a table at run time.
Use one of these methods to specify the TRUNCATE TABLE statement:
  • Set Generate truncate table statement at runtime to Yes, and enter the name of the table to truncate in the Table name property.
  • Set Generate truncate table statement at runtime to No, and enter the TRUNCATE TABLE statement in the Truncate table statement property.
To configure the job to fail when the statement that is specified by the table action fails, you can set the appropriate property to Yes:
  • Abort when create table statement fails
  • Abort when drop table statement fails
  • Abort when truncate table statement fails
Otherwise, when the statement fails, the connector logs a warning message, and the job continues.

The connector supports additional options when the Table action mode is either configured as Create or Replace and the Generate create table option at runtime is set to Yes. For more information about additional supported options, refer to the following table:

Table 2. Additional options
Value Description
Row format

Use the Row format option to specify the row format of the table created. This option supports the following different values:

Storage format
Use this option to configure the connector to use the value specified in the Data storage format property to determine the format of the Hive table to be created.
Delimited
Use this option to create the Hive table in the Delimited format. When this option is selected, the connector enables the Field terminator and the Line terminator options.
Serde
Use this option to specify the Serializer and Deserializer to be used to determine the format of the Hive table to be created.
Data Storage format
This property supports six different file formats for storing data on Hive:
  • TEXTFILE
  • SEQUENCEFILE
  • AVRO
  • PARQUET
  • RC FILE
  • ORC
Use this property to specify the storage format of the table created. TEXTFILE is the default file format.
Table location Use this option to specify the location of the directory that serves as the storage for the Hive table. By default you can leave this option blank. When the connector passes a blank value for the location, Hive server uses the default location for the Hive tables configured in the Hive servers.
Field Terminator This property is enabled when Row format is selected as "Delimited". It is an optional property.
Line Terminator This property is enabled when Row format is selected as “Delimited”. It is an optional property.
SerdeLibrary This property is enabled when Row format is selected as “SerDe”. Specify the Seder library name to create the table.
Table 3. Examples
Properties Create table statement

Generate create statement = Yes, RowFormat=Delimited, Field Terminator=\054, Line Terminator=\n

CREATE TABLE test_table_action_1 (c1 int, c2 string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\054' LINES TERMINATED BY '\n' STORED AS TEXTFILE

Generate create statement = Yes, RowFormat=SerDe, Serde Library =org.apache.hadoop.hive.serde2.columnar.ColumnarSerDe, StorageFormat=AVRO

CREATE TABLE test_table_action_2 (c1 int, c2 string) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.columnar.ColumnarSerDe' STORED AS AVRO

Generate create statement = Yes, RowFormat=StorageFormat, StorageFormat =RCFile, Location=/apps/hive/test_table_action_3

CREATE TABLE test_table_action_3 (c1 int, c2 string) STORED AS RCFILE LOCATION '/apps/hive/test_table_action_3'