Import/Export

Table of Contents

There are several ways to enter data in a file into Machbase DB, or to save data from Machbase DB into a file as follows.

Load Data & Save Data

LOAD DATA INFILE

​CSV format data files are read directly from the server, and tables and columns are created directly from the server according to the options.

  • You do not need to create a table in advance.
  • When using pre-generated tables, all columns must be of type varchar.
  • Use the field name of the first line of the csv file as the column name.
  • All columns are created with varchar (32767).
  • The default path of file name is $MACHBASE_HOME.

Syntax:

LOAD DATA INFILE 'file_name'
    INTO TABLE tbl_name [TABLESPACE tbs_name]  [AUTO mode_string]
    [
        {FIELDS | COLUMNS}
        [TERMINATED BY 'char']
        [ENCLOSED BY 'char']
    ]
    [TRIM ON | OFF]
    [IGNORE number {LINES | ROWS}]
    [MAX_LINE_LENGTH number]
    [ENCODED BY coding_name]
    [ON ERROR STOP|IGNORE]

Example:

LOAD DATA INFILE 'sensor.csv'
    INTO TABLE sensor_data AUTO HEADUSE
    ON ERROR IGNORE

LOAD DATA INFILE reads data in comma-separated value (CSV) format from the server into a column or table based on the options while creating columns or tables.
Definitions for each option are as follows.

Table 1. Insert option

Option Description
AUTO mode_string/ mode_string=(BULKLOAD or HEADUSE or HEADUSE_EXCAPE) It creates a type (varchar type) and a name of the column while creating the table. BULKLOAD: It inserts a single row of data into a column. It is used when data cannot be separated into columns. HEADUSE: It uses a name of column which is described in the first line of a data file as the column name of the table. It creates the number of columns described in the header. HEADUSE_ESCAPE: It is similar to "HEADUSE" feature, but is used to prevent using a same name as the reserved keywords. In that case, '' is used before/after the name of a column and if a special character is used for a column name, it converts into "".
(FIELDS or COLUMNS) TERMINATED BY 'term_char' ESCAPED BY 'escape_char' To parse data lines, it needs to specify separated characters (term_char) and escape characters (escape_char). For CSV files, a comma (,) is generally used for separator and ' is for escape characters.
ENCODED BY coding_name coding_name = { UTF8(default) or MS949 or KSC5601 or EUCJP or SHIFTJIS or BIG5 or BG231280 } It specifies encoding options for data file. The default is set to UTF-8.
TRIM (ON or OFF) It removes or maintains an empty space of a column. The default is set to ON.
IGNORE number (LINES or ROWS) It ignores data with the number of specified lines or rows. It is used to ignore the header of CSV file or VCF header.
MAX_LINE_LENGTH It specifies the maximum length of a line. The default is set to 512K. If data is larger then the specified length, modify it to a large number.
ON ERROR (STOP or IGNORE) It specifies a following operation when an error occurs while inserting data. If it is STOP, it suspends inserting operation and if it is IGNORE, skip the line where the error occurs and keep insert data. The default is set to IGNORE.
LOAD DATA INFILE 'tmp/aaa.csv' INTO TABLE Sample_data;
-- To insert data using "default field delimiter (,) field enclosure (").

LOAD DATA INFILE '/tmp/bbb.csv' INTO TABLE NEWTABLE AUTO BULKLOAD;
-- Insert a line into a column by creating NEWTABLE which has a single column.

LOAD DATA INFILE 'tmp/bbb.csv' INTO TABLE NEWTABLE AUTO HEADUSE;
-- Create NEWTABLE by using the first line of csv as column information and insert it into the table.

LOAD DATA INFILE 'tmp/ccc.csv' INTO TABLE Sample_data FIELDS TERMINATED BY ';' ENCLOSED '\' IGNORE 1 LINES ON ERROR IGNORE;
-- Ignore the first line and use ; for field separator and characters must be enclosed with quotation marks '.

SAVE DATA

It converts query results into CSV data and save them.

Syntax:

SAVE DATA  INTO 'file_name.csv'
    [   {FIELDS | COLUMNS}
        [TERMINATED BY 'char']
        [ENCLOSED BY 'char']
    ]
    [HEADER ON|OFF]
    [ENCODED BY coding_name]
AS select_statement;

Example:

SAVE DATA INTO '/tmp/sensor.csv' HEADER ON AS SELECT * FROM sensor_data

Description for each option is as follows.

Option Description
(FIELDS or COLUMNS) TERMINATED BY 'term_char' ENCLOSED BY 'escape_char' It specifies a column separator and escape separator for a CVS file which will be created.
HEADER (ON or OFF) It determines whether to put a column name in the first line of a CSV file which will be created. The default is set to OFF.
ENCODED BY coding_name coding_name = ( UTF8, MS949, KSC5601, EUCJP, SHIFTJIS, BIG5, BG231280 ) It specifies encoding format of output data files. The default is set to UTF-8.
SAVE DATA INTO '/tmp/aaa.csv' AS select * from t1; 
- After running SELECT statement, it records the query results in '/tmp/aaa.csv' with CSV format.

SAVE DATA INTO '/tmp/ccc.csv' FIELDS TERMINATED BY ';' ENCLOSED '\'' HEADER ON ENCODED BY 'MS949' AS select * from t1 where i1 > 100;
- It records the results after issuing SELECT statement in '/tmp/ccc.csv' file. It specifies field delimiter and escape delimiter respectively and 
set to MS949 for data encoding.

machloader

You can import and export the table data using machloader. Refer to machloader for more detailed usage.

machloader -i -t sensor_data -d sensor.csv
machloader -o -t sensor_data -d sensor.out

machsql

There is an option to save SQL result into a file during machsql execution.

machsql -f query.sql -o data.out

results matching ""

    No results matching ""