Creates a scheduled load for a specified table from a specified drop folder.
Syntax
| Code Block |
|---|
| language | sql |
|---|
| linenumbers | true |
|---|
|
CREATE SCHEDULED LOAD [schema_name.]target_table_name
FROM <drop-folder>
SCHEDULE
{
[CONTINUES] |
[PERIODIC x {MINUTES | HOURS}]
}
DESCFILE <desc-file-path>
[<optional-parameters>]
<drop-folder> :: =
{
<folder>[/file-pattern]
}
<desc-file-path> :: =
{
<folder>/description-file-name
}
<optional-parameters> :: =
{
[PRELOAD_FILE_ACTION {RENAME | NONE}]
[POSTLOAD_FILE_ACTION {RENAME | DELETE | MOVE <folder>}]
[FAILED_FILE_ACTION {RENAME | DELETE | MOVE <folder>}]
}
<folder> :: =
{
[HDFS://[<ip>]]/<path>
} |
...
| Parameter | Mandatory? | Details |
|---|
| Target Table Name | | The name of the target table, for which the data will be loaded into. |
| Drop Folder | | Location of the input file to load with optional files pattern. A drop folder can be defined on locally mounted file system or on HDFS:
- Local path - Full path of a folder on a locally mounted file system. For example: /home/jethro/salesdata/
- HDFS full path - Full path of an HDFS folder. For example: hdfs://my.cluster:8020/data/sales/
|
| Schedule | | The load schedule frequency.- CONTINUES - Continually checks if new files are available in the drop folder, and start loading if at least one new file is found in drop folder
- PERIODIC x MINUTES | HOURS - Checks for new files in the drop folder every X minutes/hours. Start load if at least one new file is found in the drop folder.
|
| Description file | | DESCFILE {desc-file-path} - Full path to load description file. If the file is located on HDFS, use HDFS: prefix notation. |
| Pre-load file action | | PRELOAD_FILE_ACTION {RENAME | NONE}RENAME (default) - Rename file before load to *.loading
NONE - Don't rename file before load |
| Post-load file action | | POSTLOAD_FILE_ACTION {RENAME | DELETE | MOVE}RENAME (default)- Rename to *.done
DELETE - Delete loaded file
MOVE [HDFS://[{ip}]]loaded-files-folder - Move the loaded file into a predefined loaded files folder |
| Failed file action | | FAILED_FILE_ACTION {RENAME | DELETE | MOVE}RENAME (default) -Rename to *.failed
DELETE - Delete failed file
MOVE [HDFS://[<ip>]]failed-files-folder - Move the failed file into a predefined failed files folder |
...
| Code Block |
|---|
| language | sql |
|---|
| linenumbers | true |
|---|
|
CREATE SCHEDULED LOAD sales FROM hdfs://mycluster:8020/data/sales/ SCHEDULE PERIODIC 15 MINUTES DESCFILE hdfs://mycluster:8020/desc/sales.desc;
CREATE SCHEDULED LOAD sales FROM /home/jethro/instances/myinstance/loads/drop/sales/*.csv SCHEDULE CONTINUES DESCFILE /home/jethro/instances/myinstance/loads/desc/sales.desc;
CREATE SCHEDULED LOAD sales FROM /home/jethro/instances/myinstance/loads/drop/sales/ SCHEDULE PERIODIC 1 HOURS DESCFILE /home/jethro/instances/myinstance/loads/desc/sales.desc PRELOAD_FILE_ACTION NONE;
CREATE SCHEDULED LOAD sales FROM /home/jethro/instances/myinstance/loads/drop/sales/ SCHEDULE PERIODIC 1 HOURS DESCFILE /home/jethro/instances/myinstance/loads/desc/sales.desc POSTLOAD_FILE_ACTION MOVE /home/jethro/instances/myinstance/loads/done;
CREATE SCHEDULED LOAD sales FROM /home/jethro/instances/myinstance/loads/drop/sales/ SCHEDULE PERIODIC 1 HOURS DESCFILE /home/jethro/instances/myinstance/loads/desc/sales.desc FAILED_FILE_ACTION DELETE; |
See Also
SHOW SCHEDULED LOADS - Print all scheduled loads
DROP SCHEDULED LOAD {table-name} - Remove scheduled load of specific table
DROP SCHEMA SCHEDULED LOADS {schema-name} - Remove scheduled loads of specific schema (or default schema if parameter is empty).