0

For testing purposes, I have to insert large Time Series data on a MySQL database, running on a Windows remote server. Firstly, the (very basic) table:

CREATE TABLE 'sensor_data' (
    'unix_timestamp' bigint(20), -- unixtimestamp in ns precision, 19 digits
    'sensor' varchar(45),
    'val' double,
    'tag1' varchar(20),
    'tag1' varchar(20),
    'tag1' varchar(20)
)

I have some CSV files, with 300 millions lines (nearly 15 GB), with comma-separated fields, with the same exact columns, in the same order of the created table. For example:

unix_timestamp,sensor,val,tag1,tag2,tag3
1598362837498000000,sensorname,0.333,info1,info2,info3
....

I tried the mysqlimport tool, with the following Powershell command (from a client pc on the same LAN):

.\mysqlimport -v --fields-terminated-by=',' --lines-terminated-by='\r\n' --ignore-lines=1 -u user -p --local -h host db path/to/csv/tablename.csv --columns=unix_timestamp,sensor,val,tag1,tag2,tag3 --force --use-threads=4 --port 3306

The result is always the same:

Connecting to host
Selecting database db
Loading data from LOCAL file: 
path/to/csv/tablename.csv into tablename
db.tablename: Records: 0  Deleted: 0  Skipped: 0  Warnings: 0
Disconnecting from host

When I add the -? flag to the same cmd, the explain function describes all my parameters, and they are all ok.

Weird fact: When I try to import much smaller CSV files (e.g. 400 lines), import succeeds, all lines get imported, but the result is:

unix_timestamp         sensor                             val tag1 tag2 tag3
1598362837498000000    sensorname,0.333,info1,info2,info3
....

Timestamp column is ok, but all other fields are written in sensor field!

How can I fix both weird behaviors?

Please note that I need a way to automate ingestions, so I cannot use interactive ways of inserting data.

  • 1
    Try to use LOAD DATA INFILE query instead of mysqlimport tool. Does you will see the same problem? – Akina Feb 15 at 11:13
  • And explain, how 33 bytes sensorname,0.333,info1,info2,info3 were written in sensor field which is varchar(20)? where is the inaccuracy? – Akina Feb 15 at 11:16
  • @Akina: sadly, same results. Varchar(20) was a rudimental fix, it was originally Varchar(45). Editing my question, thanks for pointing it out. – LucaF Feb 15 at 12:12
  • Try SET form of LOAD DATA INFILE (with temporary user variables). Additionally - export some data from sensor_data destination table (10-20 records) and check if string fields are enclosed with double-quotes or something similar, if so, try to use LOAD DATA INFILE with more parameters (fields separators, enclose symbols, etc.). – Akina Feb 15 at 12:25
  • sensor_data table is empty at the moment I try to upload 300M records, so I cannot check what you are asking for, sadly. I can try to manually insert some data, and then export it. I will let you know. Anyway, assumed that exported data is enclosed by quotes, I cannot enclose 300M x 6 variables (for each CSV file). – LucaF Feb 15 at 13:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.