MACHLOADER
- Ryan Park (Unlicensed)
- Grey Shim
Option | Description |
|---|---|
| -s, --server=SERVER | Enters Machbase server IP address (default: 127.0.0.1) |
| -u, --user=USER | Enters connecting user name (default: SYS) |
| -p, --password=PASSWORD | Connecting user password (default: MANAGER) |
| -P, --port=PORT | Machbase server port number (default: 5656) |
| -i, --import | Data import command option |
| -o, --export | Data export command option |
| -c, --schema | Command option to create schema file using the database table information |
| -t, --table=TABLE_NAME | Sets table name that is creating a schema file |
| -f, --form=SCHEMA_FORM_FILE | Specifies schema filename |
| -d, --data=DATA_FILE | Specifies a data file name |
| -l, --log=LOG_FILE | Specifies a machloader execution log file |
| -b, --bad=BAD_FILE | Records the data in which the input error occurred and specifies the file name that records the error description when executing -i option. |
| -m, --mode=MODE | Indicates import method when executing -i option. The append or replace option is available. Append enters the data after the existing data and replace deletes the existing data and enters the data. |
| -D, –delimiter=DELIMITER | Sets each field delimiter. The default value is ','. |
| -n, --newline=NEWLINE | Sets each record separator. The default is '\n'. |
| -e, --enclosure=ENCLOSURE | Sets the enclosing delimiter for each field. |
| -r, --format=FORMAT | Specifies the format for file input/output. (default: csv) |
| -a, --atime | Determines whether to use the built-in column "_ARRIVAL_TIME". The default value is to not use the column. |
| -I, --silent | Does not display copyright-related output and import/export status information. |
| -h, --help | Displays a list of options. |
| -F, --dateformat=DATEFORMAT | Sets the column dateformat. ("_arrival_time YYYY-MM-DD HH24:MI:SS")
The unixtimestamp and nanotimestamp formats are supported since 5.7. |
| -E, --encoding=CHARACTER_SET | Sets the encoding of input/output files. Supported encodings are UTF8 (default), ASCII, MS949, KSC5601, EUCJP, SHIFTJIS, BIG5, GB231280, and UTF16. |
| -C, --create | Creates a table if one does not exist upon import. |
| -H, --header | Sets whether header information is present upon import/export. The default value is unset. |
| -S, --slash | Specifies the backslash delimiter. |
Basic Usage
The table must be created first before running the usage below.
CSV File Import
Imports CSV file to Machbase server.
Option:
-i: import specification options -d: data file naming options -t: table name specification option
Example:
machloader -i -d data.csv -t table_name
CSV File Export
Writes data to a CSV file.
Option:
-o: export specification options -d: data file naming options -t: table name specification option
Example:
machloader -o -d data.csv -t table_name
Use CSV File Header
The header-related setting of the CSV file.
Option:
-i -H: Upon import, the first line of the csv file is recognized as a header. Therefore, the first line is excluded from input. -o -H: Upon export, generates the csv header as the column name of the table.
Example:
machloader -i -d data.csv -t table_name -H machloader -o -d data.csv -t table_name -H
Automatic Table Creation
Regards automatic table creation.
Option:
-C: Automatically generates the table when importing. The column names are automatically generated as c0, c1, .... The generated column is varchar (32767) type. -H: Generates column names with csv header name when importing.
Example:
machloader -i -d data.csv -t table_name -C machloader -i -d data.csv -t table_name -C -H
Files Not CSV Format
Sets delimiter for files that are not in CSV format.
Option:
-D: Delimiter option for each field -n: Specifies each record delimiter option -e: Specifies the enclosing character for each field.
Example:
machloader -i -d data.txt -t table_name -D '^' -n '\n' -e '"' machloader -o -d data.txt -t table_name -D '^' -n '\n' -e '"'
Specify Input Mode
When importing (with -i option), there are two modes, REPLACE and APPEND. APPEND is the default. Use REPLACE mode with caution because it deletes existing data.
Option:
-m: Specifies import mode
Example:
machloader -i -d data.csv -t table_name -m replace
Specify Connection Information
Specifies server IP, user, and password separately.
Option:
-s: Specifies server IP address (default: 127.0.0.1) -P: Specifies server port number (default: 5656) -u: Specifies the connecting user name (default: SYS) -p: Specifies the password of the connecting user (default: MANAGER)
Example:
machloader -i -s 192.168.0.10 -P 5656 -u mach -p machbase -d data.csv -t table_name
Create Log File
Creates the execution log file for machloader.
Option:
-b: Sets the name of the log file to generate the data that is not input when importing. -l: Sets the name of the log file to generate the data and error message that were not input when importing.
Example:
machloader -i -d data.csv -t table_name -b table_name.bad -l table_name.log
Create Schema File
The machloader schema file can be created. Import/export is possible even if the data type format is changed using a schema file or the number of columns in the table and data file is different.
Option:
-c: schema file creation options -t: table name specification option -f: created schema file name specification option
Example:
machloader -c -t table_name -f table_name.fmt machloader -c -t table_name -f table_name.fmt -a
Set datetime Format in Schema File
The date format can be set to preference with the DATEFORMAT option.
Syntax:
# Set for all datetime columns. DATEFORMAT <dateformat> # Set for individual datetime column. DATEFORMAT <column_name> <format>
Example:
-- Set dateformat for each field in datetest.csv file in the schema file (datetest.fmt).
datetest.fmt
table datetest
{
INS_DT datetime;
UPT_DT datetime;
}
DATEFORMAT ins_dt "YYYY/MM/DD HH12:MI:SS"
DATEFORMAT upt_dt "YYYY DD MM HH12:MI:SS"
datetest.csv
2017/02/20 11:05:23,2017 20 02 11:05:23
2017/02/20 11:06:34,2017 20 02 11:06:34
-- Import datetest.csv file and check input data.
machloader -i -f datetest.fmt -d datetest.csv
-----------------------------------------------------------------
Machbase Data Import/Export Utility.
Release Version 5.1.9.community
Copyright 2014, MACHBASE Corporation or its subsidiaries.
All Rights Reserved.
-----------------------------------------------------------------
Import time : 0 hour 0 min 0.39 sec
Load success count : 2
Load fail count : 0
mach> SELECT * FROM datetest;
INS_DT UPT_DT
-------------------------------------------------------------------
2017-02-20 11:06:34 000:000:000 2017-02-20 11:06:34 000:000:000
2017-02-20 11:05:23 000:000:000 2017-02-20 11:05:23 000:000:000
[2] row(s) selected.
Elapsed time: 0.000
IGNORE
When you do not want to enter a specific field in the CSV file, you can set the IGNORE option in the fmt file.
The ignoretest.csv file has three fields, but if the last field is not needed, specify IGNORE in the column that is not needed in the fmt file.
Example:
-- Set ignore option for last field in ignoretest.fmt file.
ignoretest.fmt
table ignoretest
{
ID integer;
MSG varchar(40);
SUB_ID integer IGNORE;
}
ignoretest.csv
1, "msg1", 3
2, "msg2", 4
-- Import ignoretest.csv file and check input data.
machloader -i -f ignoretest.fmt -d ignoretest.csv
-----------------------------------------------------------------
Machbase Data Import/Export Utility.
Release Version 5.1.9.community
Copyright 2014, MACHBASE Corporation or its subsidiaries.
All Rights Reserved.
-----------------------------------------------------------------
NLS : US7ASCII EXECUTE MODE : IMPORT
SCHMEA FILE : ignoretest.fmt DATA FILE : ignoretest.csv
IMPORT_MODE : APPEND FILED TERM : ,
ROW TERM : \n ENCLOSURE : "
ARRIVAL_TIME : FALSE ENCODING : NONE
HEADER : FALSE CREATE TABLE : FALSE
Progress bar Imported records Error records
2 0
Import time : 0 hour 0 min 0.39 sec
Load success count : 2
Load fail count : 0
mach> SELECT * FROM ignoretest;
ID MSG
---------------------------------------------------------
2 msg2
1 msg1
[2] row(s) selected.
Elapsed time: 0.000
If Number of Columns Is More Than Number of Fields
If the number of columns in the table is greater than the number of fields in the data file, only the columns specified in the schema file are entered, and the other columns are entered as NULL.
If Number of Columns Is Less Than Number of Fields
If the number of columns in the table is less than the number of fields in the data file, fields not in the table must be excluded with the IGNORE option.
Example:
-- Import ignoretest.csv file and exclude input data by setting ignore option for last field.
loader_test.fmt
table loader_test
{
ID integer;
MSG varchar (40);
SUB_ID integer IGNORE;
}