Maps a foreign data source table (such as Hive tables or files) into Jethro.
Table of Contents
Syntax
| Code Block | ||||
|---|---|---|---|---|
| ||||
CREATE EXTERNAL TABLE [schema_name.]new_external_table_name
(<columns_list_spec>)
DATA SOURCE=existing_external_data_source_name
LOCATION=<location_spec>
[<values_format_spec>]
[<rejects_handling_spec>]
[<rejects_thresholds_spec>]
<columns_list_spec> ::=
<column_spec> [,<column_spec>...n]
<column_spec> ::=
{
column name
column_type
[<values_format_spec>]
}
<values_format_spec> ::=
{
[NULL DEFINED AS ('<string>' [,'<string>'...])]
[DATETIME FORMAT <ISO 8601 date format string>]
[TIMEZONE=<timezone>]
}
<location_spec> ::=
{
<table_name_from_the_external_data_source> |
<list_of_files>
}
<list_of_files> ::=
'<path>' [,'<path>'...]
<path> ::=
[HDFS://]folder[/file_name]
<rejects_handling_spec> ::=
REJECT_POLICY=
{
FILL_NULL |
SKIP_ROW |
FAIL
}
<rejects_thresholds_spec> ::=
{
[REJECT_LIMIT_COUNT=<a integer value specifying max number of allowed rows with reject>] |
[
REJECT_SAMPLE=<minimum number of rows to process before making reject decision>
REJECT_LIMIT_RATIO=<a decimal between 0 and 1>
]
} |
...
list_of_files - The name or names of files to fetch the data from. When reading more than one file, it is important to varify that they are all using the same structure, order, and date types as defined during the external table creation. It is possible to use a direct full path for a specific file, or to use a hint which will allow access to multiple files, such as: folder path, or a file path with an hint char ('?' represents any single char, '*' represents any set of chars). Regular expression is not supported for the directories components of the path, only for the files. Anyhow the file location must include the full path.
<values_format_spec> - Specifies how to convert values, according to the following parameters:
...
[DATETIME FORMAT <ISO 8601 date format string>] - The default format is 'yyyy-MM-dd HH:mm:ss'. For information about available TIMESTAMP formats and timezone, visit Loading Data.
[TIMEZONE=<timezone>] - Converts a timestamp in UTC to a given timezone.
...