/
Load by SQL

Load by SQL

Owned by Ryan Park (Unlicensed)
Last updated: Nov 09, 2021 by Grey Shim

The 'Load Data' statement puts the data in the csv file into Machbase.

First, create a table to store the data, using the first line of the csv file to create the columns.

  • The data type of the generated columns is VARCHAR (32768).
  • The data file path is a relative path based on $MACHBASE_HOME. It can also be set to an absolute path.

To save the table data as csv file, use the SAVE DATA statement.

If you already know the data type for each field in the csv file, you can create the table in advance and enter the data.

If you enter the file 'load_sample.csv' into the LOAD DATA statement, the table 'load_sample' is automatically created.

Contents



Index


Loading Data

LOAD DATA INFILE 'sample/quickstart/load_sample.csv' INTO TABLE load_sample AUTO HEADUSE;


Confirm Data Loading

SELECT * FROM load_sample;


Sample Example


Using the sample file, you can do the following.

[mach@localhost ~]$ cd $MACHBASE_HOME/sample/quickstart
[mach@localhost ~]$ ls -l load_sample.csv
-rw-r
--r--- 1 root root 2827 2017-02-23 15:01 load_sample.csv

[mach@localhost ~]$ machsql
=================================================================
     Machbase Client Query Utility
     Release Version x.x.x.official
     Copyright 2014, Machbase Inc. or its subsidiaries.
     All Rights Reserved
=================================================================
Machbase server address (Default:127.0.0.1) :
Machbase user ID  (Default:SYS)
Machbase User Password :
MACH_CONNECT_MODE=INET, PORT=5656

Mach> LOAD DATA INFILE 'sample/quickstart/load_sample.csv' INTO TABLE load_sample AUTO HEADUSE;
50 row(s) loaded. Failed to load 0 row(s).
Mach> DESC load_sample;
----------------------------------------------------------------
NAME                          TYPE                LENGTH
----------------------------------------------------------------
SENSOR_ID                     varchar             32767
EPOCH_TIME                    varchar             32767
E_YEAR                        varchar             32767
E_MONTH                       varchar             32767
E_DAY                         varchar             32767
E_HOUR                        varchar             32767
E_MINUTE                      varchar             32767
E_SECOND                      varchar             32767
VALUE                         varchar             32767
Mach> SELECT COUNT(*) FROM load_sample;
COUNT(*)
-----------------------
50
[1] row(s) selected.
Mach>