MACHLOADER

List of machloader

machloader is a tool which imports a data file into Machbase DB as a text file format or exports a data file from machbase DB as a text file format.
Basically it works as a csv format file but alternative format files are also available. machloader has the following characteristics.

  • machloader doesn't use "_arrival_time" field as default. Therefore, to use "_arrival_time", "-a" option has to be given.
  • machloader operates in APPEND mode by default.
  • To delete the primary data and insert new data, "-m replace" option has to be given.
  • machloader doesn't check the integrity of both schema and data files. Therefore, the user must check whether it exactly matches each field of the schema file with the value of data file.
  • machloader can specify the datetime format in the schema file. The specified datetime format must conform with the format which is supported by Machbase, and the same format should be applied to all fields as default. but you can also define a different format to each field.

Options used in machloader can be checked as follow.

[mach@localhost]$ machloader -h

Table 1. MACHLOADER option and description

Option Description
-s, --server=SERVER Set server IP address(default: 127.0.0.1). It specifies the IP address of the server where machbased is running. (e.g. localhost)
-u, --user=USER Set user ID(default: SYS). It specifies the user ID to login to machbased. (e.g. SYS)
-p, --password=PASSWORD Set user password. It specifies the user's password to login to machbased. (e.g. MANAGER)
-P, --port=PORT Set port number. It specifies the port number to access the machbased. The default value is 5656, and it is set in machloader.conf file.
-i, --import Data import mode. Import data from external csv file.
-o, --export Export data to csv file. Download data from machbased to external files.
-c, --schema Create the data schema file from table schema. Download schema of a specified table in machbased to external files.
-t, --table=TABLE_NAME Set a table name to get schema. Set a table name to execute when -c option is used.
-f, --form=SCHEMA_FORM_FILE Set a name of schema file. Set the name of the schema form file to save.
-d, --data=DATA_FILE Set the name of data file. When -i or -o option is used, it sets the name of data file.
-l, --log=LOG_FILE Set log file name. Set the name of a log file.
-b, --bad=BAD_FILE Set the file name of data which are not entered. When -i option is used, it sets the log file name of data which is not entered with description.
-m, --mode=MODE Set import mode. (append or replace) (default: append)/ When -i option is used, it sets Replace or Append mode. If none is specified, Append mode will be set by default. Replace mode deletes all existing data and inserts data. Append mode maintains the existing data and inserts additional data. It can specify whether to use "_arrival_time" or not by using -a option or not.
-D, --delimeter=DELIMETER Set field delimeter character. (default: ',')/ Set the delimiter to each field. The default value is ','.
-n, --newline=NEWLINE Set new line character. (default: \n) Set the delimiter of each row. The default value is '\n'.
-e, --enclosure=ENCLOSURE Set enclosing character. Set the enclosing character of each field.
-r, --format=FORMAT Set csv format mode. (default: csv)/ Imports and exports data as csv format.
-a, --atime Use _arrival_time field. It uses "_arrival_time" field, which is used internally.
-I, --silent Produce less output. It doesn't display the copyright information and execution log on the screen.
-h, --help Display help message. Display Help screen.
-F, --dateformat=DATEFORMAT Set each column dateformat.(default: "_arrival_time YYYY-MM-DD HH24:MI:SS")/ Set the format of entire datetime.
-E, --encoding=CHARACTER_SET Set input character set. Set the encoding of data files to be inserted. It converts the contents of other encoding files into UTF-8 and upload it. It supports these encodings-UTF8(default), ASCII, MS949, KSC5601, EUCJP, SHIFTJIS, BIG5, GB231280 and UTF16
-C, --create If there is no table, create a table.
-H, --header Specifies that header info is present when data is input or output.

Basic Use

Table must be created in advance for these options for 'import csv file' and 'export data'.

Import CSV File

Import a csv file into Machbase DB.

Option:

-i: Import data from external csv file.
-d: Set a name of data file.
-t: Set a table name.

Example:

machloader -i -d data.csv -t table_name

Export data

Export data to a csv file.

Option:

-o: Export data.
-d: Set a name of data file.
-t: Set a table name.

Example:

machloader -o -d data.csv -t table_name

Header in csv file

This option is to use header in csv file.

Option:

-i -H: Set the first line in the csv file as a header and the line is excluded from importing data.
-o -H: Set the names of table columns as a header in exporting to csv file.

Example:

machloader -i -d data.csv -t table_name -H
machloader -o -d data.csv -t table_name -H

Automatic table creation

This option is to create a table automatically.

Option:

-C: Create a table from data file. Columns are automatically set as c0, c1, ...with the data type of varchar(32767).
-H: If the header is in the csv file, set the field name of the header to the column name of the table.

Example:

machloader -i -d data.csv -t table_name -C
machloader -i -d data.csv -t table_name -C -H

None csv format files

Delimiters can be applied to files that are not csv files.

Option:

-D: Set the delimiter to each field.
-n: Set the delimiter of each row.
-e: Set the enclosing character of 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 '"'

Append or replace mode

There are two available modes: REPLACE and APPEND. If none is specified, APPEND mode will be used by default.
For REPLACE mode, it deletes all the existing table data and then, insert data. For APPEND mode, it adds data while maintaining the existing table data.

Option:

-m: Set import mode.

Example:

machloader -i -d data.csv -t table_name -m replace

Set server, user and password

Set different server IP address, user ID and password.

Option:

-s: Set server IP address. (default: 127.0.0.1)
-P: Set port number (default: 5656)
-u: Set user ID(default: SYS)
-p: Set user password(default: MANAGER)

Example:

machloader -i -s 192.168.0.10 -P 5656 -u mach -p machbase -d data.csv -t table_name

Execution log

Used to leave the execution log when running machloader.

Option:

-b: Set the file name of data which are not entered.
-l: Set the log file name of data is not entered with description.

Example:

machloader -i -d data.csv -t table_name -b table_name.bad -l table_name.log

Data file schema

You can set the data file schema with these options. When creating a data schema file, set a different date type, or use if the number of fields in the data file and the number of columns in the table are different.

Option:

-c: Create the data schema file from table schema.
-t: Set a table name.
-f: Set a name of schema file.

Example:

machloader -c -t table_name -f table_name.fmt
machloader -c -t table_name -f table_name.fmt -a

Set datetime format on each field In the .fmt file

DATEFORMAT
​DATEFORMAT option allows you to specify a default date format or a column-specific date format.

Syntax:

# full set​
DATEFORMAT <dateformat>

# column set
DATEFORMAT <column_name> <format>

The above two types are available, and if the entire column is used together, the entire setting must be placed before the column setting. The DATEFORMAT setting must be placed after the table setting in the fmt file.​

Example_1:

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

If you set the DATEFORMAT in .fmt file like above and input two datetimes with different formats like datetest.csv, you can see that it is input well.

Example_2:

machloader -i -f datetest.fmt -d datetest.dat
-----------------------------------------------------------------
Machbase Data Import/Export Utility.
Release Version 3.1.1.5a0a49c.develop
Copyright 2014, Machbase Inc. 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
mach>

IGNORE

If you have data that you do not want to store in the table, you can use the IGNORE option to exclude it from the input.

Example_1:

ignoretest.fmt
table ignoretest
{
ID integer;
MSG varchar(40);
SUB_ID integer IGNORE;
}

ignoretest.csv
1, "msg1", 3
2, "msg2", 4

Ignoretest.csv has three types of data(three columns), but if the actual table has only ID and MSG columns. You can omit it from data input by adding SUB_ID and setting IGNORE in fmt file.

Example_2

machloader -i -f ignoretest.fmt -d ignoretest.csv
-----------------------------------------------------------------
Machbase Data Import/Export Utility.
Release Version 3.5.0.82bcea8.alpha
Copyright 2014, Machbase Inc. 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
mach>

The number of table columns > The number of data fields

Enter data only for columns set in the schema file, and NULL for columns, which are not set.

The number of table columns < The number of data fields

IGNORE option is applied to columns that do not exist in the table in order to exclude them from data input.

Example:

loader_test.fmt
table loader_test
{
ID integer;
MSG varchar (40);
SUB_ID integer IGNORE;

}

results matching ""

    No results matching ""