Default Value

A column in the external table with the same name as the Hive column

Syntax

A JSON document with the keyword-value pairs is shown in the following diagram:

colmap ::=

colmap_entry ::=

Semantics

"col":name

"col": The keyword must be lowercase and enclosed in quotation marks.

name: The name of a column in the Oracle external table. It is case sensitive and must be enclosed in quotation marks.

"field":name

"field": The keyword must be lowercase and enclosed in quotation marks.

name: The name of a field in the data source. The name is not case-sensitive, but it must be enclosed in quotation marks. See Syntax Rules for Specifying Properties.

Examples

This example maps a Hive column named ORDER_LINE_ITEM_COUNT to an Oracle column named ITEM_CNT:

com.oracle.bigdata.colMap={"col":"ITEM_CNT", \
                           "field":"order_line_item_count"}

The following example shows the mapping of multiple columns.

com.oracle.bigdata.colmap:[{"col":"KOL1", "field":"PROJECT_NAME"},{ "col":"KOL2","field":"wsdl_name"},{"col":"KOL3", "field":"method"}]

Default Value

{"action":"setnull"}

Syntax

A JSON document with the keyword-value pairs is shown in the following diagram:

erroropt ::=

error_element ::=

Semantics

The "action", "reject", "setnull", "replace", "value", and "col" keywords must be lowercase and enclosed in quotation marks. See Syntax Rules for Specifying Properties.

"action":value

value: One of these keywords:

• "reject": Does not load any rows.

• "setnull": Sets the column to NULL.

• "replace": Sets the column to the specified value.

"value":string

string: Replaces a bad value in the external table. It must be enclosed in quotation marks.

"col":name

name: Identifies a column in an external table. The column name is case sensitive, must be enclosed in quotation marks, and can be listed only once.

Example

This example sets the value of the CUST_NUM or ORDER_NUM columns to INVALID if the Hive value causes an error. For any other columns, an error just causes the Hive value to be rejected.

com.oracle.bigdata.errorOpt: {"action":"replace",\
                              "value":"INVALID", \
                              "col":["CUST_NUM","ORDER_NUM"]

Default Value

Not defined

Syntax

A JSON document with the keyword-value pairs is shown in the following diagram:

fields ::=

data_type ::=

primitive_type ::=

Semantics

The syntax is the same as a field list for a Hive table. If you split the field list across multiple lines, you must use a backslash to escape the new line characters.

field_name

The name of the Hive field. Use only alphanumeric characters and underscores (_). The maximum length is 128 characters. Field names are case-insensitive.

data_type

(Optional) The data type of the Hive field. The default is STRING. The character set must be UTF8.

The data type can be complex or primitive:

Hive Complex Data Types

• ARRAY: Indexable list

• MAP: Key-value tuples

• STRUCT: List of elements

• UNIONTYPE: Multiple data types

Hive Primitive Data Types

• INT: 4 byte integer

• BIGINT: 8 byte integer

• SMALLINT: 2 byte integer

• TINYINT: 1 byte integer

• BOOLEAN: TRUE or FALSE

• FLOAT: single precision

• DOUBLE: double precision

• STRING: character sequence

COMMENT col_comment

A string literal enclosed in single quotation marks, which is stored as metadata for the Hive table (comment property of TBLPROPERTIES).

Default Value

TEXTFILE

Syntax

A JSON document with the keyword-value pairs is shown in the following diagram.

fileformat::=

Semantics

ORC

Optimized row columnar file format

PARQUET

Column-oriented, binary file format

RCFILE

Record columnar file format

SEQUENCEFILE

Compressed file format

TEXTFILE

Plain text file format

INPUTFORMAT

Identifies a Java class that can extract records from the data file.

OUTPUTFORMAT

Identifies a Java class that can format the output records in the desired format

Default Value

Not defined (no logging)

Syntax

[directory_object:]file_name_template

Semantics

directory_object

The Oracle directory object for the HDFS path on the Hadoop cluster where the log file is created.

file_name_template

A string used to generate file names. This table describes the optional variables that you can use in the template.

Example

The following example generates log file names that include the PID and a unique number, such as xtlogp_hive14_3413_57:

com.oracle.bigdata.log.exec= xtlogp_hive14_%p_%a

Default Value

Not defined (no logging)

Syntax

[directory_object:]file_name_template

Semantics

directory_object

Name of an Oracle directory object that points to the path where the log files are written. If this value is omitted, then the logs are written to the default directory for the external table.

file_name_template

A string used to generate file names. Table 17-3 describes the optional variables that you can use in the string.

Example

This example creates log file names that include the PID and a percent sign, such as xtlogp_hive213459_%:

com.oracle.bigdata.log.qc= xtlogp_hive21%p_%%

Default Value

{"action":"error"}

Syntax

A JSON document with the keyword-value pairs is shown in the following diagram:

overflow ::=

overflow_element ::=

Semantics

The "action", "truncate", "error", and "col" tags must be lowercase and enclosed in quotation marks. See Syntax Rules for Specifying Properties.

"action":value

The value of "action" can be one of the following keywords:

• truncate: Shortens the data to fit the column.

• error: Throws an error. The property com.oracle.bigdata.erroropt controls the result of the error.

"col":name

name: Identifies a column in the external table. The name is case sensitive and must be enclosed in quotation marks.

Example

This example truncates the source data for the DESCRIPTION column, if it exceeds the column width:

com.oracle.bigdata.overflow={"action":"truncate", \
                             "col":"DESCRIPTION"}

Default Value

DELIMITED

Syntax

A JSON document with the keyword-value pairs is shown in the following diagram.

rowformat ::=

Semantics

DELIMITED

Describes the characters used to delimit the fields in a record:

• FIELDS TERMINATED BY: The character that delimits every field in the record. The optional ESCAPED BY character precedes the delimit character when it appears within a field value.

• COLLECTION ITEMS TERMINATED BY: The character that marks the end of an array element. Used when a column is a collection or a nested record. In this case the resulting value will be a JSON array.

• MAP KEYS TERMINATED BY: The character that marks the end of an entry in a MAP field. Used when a column is a collection or a nested record. The resulting value is a JSON object.

• LINES TERMINATED BY: The character that marks the end of a record.

• NULL DEFINED AS: The character that indicates a null value.

SERDE

Identifies a Serializer/Deserializer (SerDe) that can parse the data and any properties of the SerDe that the access driver might need.

Example

This example specifies a SerDe for an Avro container file:

com.oracle.bigdata.rowformat:
   SERDE'org.apache.hadoop.hive.serde2.avro.AvroSerDe'

The next example specifies a SerDe for a file containing regular expressions:

com.oracle.bigdata.rowformat=\
    SERDE 'org.apache.hadoop.hive.contrib.serde2.RegexSerDe' \
    WITH SERDEPROPERTIES \ 
    ("input.regex" = "(\\\\d{6}) (\\\\d{5}) (.{29}) .*") 

Default Value

DEFAULT.external_table_name

Syntax

[hive_database_name.]table_name

Semantics

The maximum length of hive_database_name and table_name is 128 UTF-8 characters (512 bytes).

hive_database_name: The Hive database where the source data resides. DEFAULT is the name of the initial Hive database.

table_name: The Hive table with the data. If you omit table_name, then ORACLE_HIVE searches for a Hive table with the same name as the external table. Table names are case-insensitive.

Example

This setting indicates that the source data is in a table named ORDER_SUMMARY in the Hive ORDER_DB database:

com.oracle.bigdata.tablename ORDER_DB.ORDER_SUMMARY