Map a foreign data source (such as Hive table) into Jethro external tables.
Syntax
| Code Block | ||||
|---|---|---|---|---|
| ||||
-- For HIVE:
CREATE EXTERNAL DATA SOURCE new_data_source_name
TYPE = HIVE
LOCATION = [sub-protocol://]<host>:<port>/<db>[;property=value[;...]]
CREDENTIALS=<username/password>
[<rejects_handling_spec>]
[<rejects_threshold_spec>]
-- For files:
CREATE EXTERNAL DATA SOURCE new_data_source_name
TYPE = FILES
[<file_format_spec>]
[<values_format_spec>]
[<rejects_handling_spec>]
[<rejects_threshold_spec>]
<file_format_spec> ::=
{
[ROW FORMAT DELIMITED
[FIELDS TERMINATED BY '<char>']
[QUOTED BY '<char>' | NONE]
[COMMENTED BY '<char>' | NONE]
[LINES TERMINATED BY '<string>']
]
[OPTIONS SKIP n]
}
<values_format_spec> ::=
{
[NULL DEFINED AS ('<string>' [,'<string>'...])]
[DATETIME FORMAT <ISO 8601 date format string>]
[TIMEZONE=<timezone>]
}
<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>
]
} |
...
[LINES TERMINATED BY '<string>'] - The default strings that marks a line termination is: '\n' for Linux, and '\r\n' for Windows.
[OPTIONS SKIP n] - Skips n header lines (default is 0 - no skipping).
<values_format_spec> - Specifies how to convert values, according to the following parameters:
...
[DATETIME FORMAT <ISO 8601 date format string>] - The format in which the source data is stored as. The default format in Jethro is 'yyyy/MM/dd HH:mm:ss'.
[TIMEZONE=<timezone>] - Coverts a timestamp in UTC to a given timezone.
| Expand | ||
|---|---|---|
|
...
|
...
| ||||||||||||||||||||||||||||||||||
Timestamp Formats The default timestamp format in Jethro is 'yyyy-MM-dd HH:mm:ss' (without a sub-second component). However, an alternative format can be specified. For example:
Timestamp format strings are case sensitive. The valid format elements are:
If the input string of the field is longer than the format defined, the rest of the field content is ignored. For example: for the format 'yyyy/MM/dd', the input field '2014-02-14 15:16:17' will be stored as '2014-02-14 00:00:00'. This allows truncating the input record to a lower precision (as in the example above - from a second-level to a day-level). Time Zones The query engine by default does not perform any timezone manipulation – the input is loaded as is. In other words, Jethro stores any TIMESTAMP data in UTC timezone and it assumes all input is already in UTC. However, in some cases you may want to adjust the timezone of an input field. For example, you may want to load log files from different data centers – each file has timestamps in the local timezone, so each needs its own adjustment. You can ask for a timezone adjustment by using the TIMEZONE property. In that case, the query engine will compute the current offset of that timezone vs. UTC once (at the beginning of its run) and will apply it to all the values of those fields. Example:
|
<rejects_handling_spec> - Specifies a policy for handling rejects at row/columns level. The allowed polices are:
FILL_NULL (default) - Replace the rejected value with a NULL.
SKIP_ROW - Skip the entire row when any value is rejected.
FAIL - Fail the entire request on the first time it encountered with a rejected value (Choosing this policy will ignore the rejects threshold defined under <rejects_thresholds_spec>).<rejects_handling_spec> sets the defaults behavior for all columns of all tables defined on the data source. Data source defaults can be override per table columns and/or per specific column via the command command CREATE EXTERNAL TABLE.
<rejects_thresholds_spec> - Specifies a rejects threshold policy for handling of multiple rejects at request level. The total number of rows with rejects are counted, and if that number reaches the defined threshold, the request fails (A row with 2 rejects will be counted as 1). The allowed rejects thresholds are:
REJECT_LIMIT_COUNT (default) - An integer value specifying the maximum number of allowed rows with rejects. If the number of rows with rejects reaches the limit, the request will fail. The default value is: 0.
REJECT_LIMIT_RATIO - A decimal value between 0 and 1, specifying the maximum ratio allowed for rows with rejects, from a sample of fetched rows (see REJECT_SAMPLE). If the actual sampled ratio is greater than the limit specified, the request will fail.
REJECT_SAMPLE - The minimum number of rows to process before making a reject decision (based on REJECT_LIMIT_RATIO). If not specified, the default value is '1000000'.
...