How to skip/dummy columns in mysqlsh importTable util?

Question

I want to explicit list the columns in importTable for LOAD DATA with mysqlsh:

https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-parallel-table.html

import.js:

util.importTable("sample.csv", {schema: "myschema", table: "mytable", 
    showProgress: true, columns: ["id","firstname",@dummy,@dummy,"lastname"]});

My csv contains 5 columns. But if my final table only contains id, firstname, lastname, I have to find a way to skip two columns of the source csv,

Import with:

mysqlsh --user=root --password='rootpw' --socket=/var/run/mysqld/mysqld.sock < import.js

Problem: SyntaxError: Invalid or unexpected token at STDIN:2:347. Neither @dummy nor '@dummy' nor "@dummy" works.

Question: how can I skip dummy columns? When using LOAD DATA it would work with using @dummy in the column options.

Answer 1

tl;dr

Since MySQL Shell 8.0.22 you can skip columns by replacing column name in columns option with integer value.

util.importTable("/tmp/names.csv", {
    table:"names",
    columns: ["id", "firstname", 1, 2, "lastname"],
    dialect: "csv-unix"
});

which is equivalent to:

LOAD DATA LOCAL INFILE '/tmp/names.csv' INTO TABLE `names`
FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\'
LINES TERMINATED BY '\n'
(id, firstname, @1, @2, lastname);

MySQL Shell >= 8.0.22

MySQL Shell’s parallel table import utility util.importTable() since version 8.0.22 has a new option decodeColumns, and an enhancement to the columns option, to enable you to capture columns from the import file for input preprocessing (or to discard them) in the same way as with a LOAD DATA statement. The decodeColumns option specifies preprocessing transformations for the captured data in the same way as the SET clause of a LOAD DATA statement, and assigns them to columns in the target table.

util.importTable column option was extended and now is accepting integers as array value. Integer value passed as k-th argument, captures k-th column in input file as user variable named @i, where i is an passed integer.

Captured column value binded to user variable can be used in decodeColumns option to preprocess and transform data before assigning the result to the column in table.

Example 1 - Preprocess column2:

util.importTable('file.txt', {
  table: 't1',
  columns: ['column1', 1],
  decodeColumns: {'column2': '@1 / 100'}
});

is equivalent to:

LOAD DATA LOCAL INFILE 'file.txt'
INTO TABLE `t1` (column1, @var1)
SET `column2` = @var/100;

Example 2 - Skip columns:

util.importTable('file.txt', {
  table: 't1',
  columns: ['column1', 1, 'column2', 2, 'column3']
});

is equivalent to:

LOAD DATA LOCAL INFILE 'file.txt'
INTO TABLE `t1` (column1, @1, column2, @2, column3);

Example 3 - Generate values for columns

util.importTable('file.txt', {
  table: 't1',
  columns: [1, 2],
  decodeColumns: {
    'a': '@1',
    'b': '@2',
    'sum': '@1 + @2',
    'mul': '@1 * @2',
    'pow': 'POW(@1, @2)'
  }
});

is equivalent to:

LOAD DATA LOCAL INFILE 'file.txt'
INTO TABLE `t1` (@1, @2)
SET
  `a` = @1,
  `b` = @2,
  `sum` = @1 + @2,
  `mul` = @1 * @2,
  `pow` = POW(@1, @2);

8.0.17 <= MySQL Shell < 8.0.22

Unfortunately, currently util.importTable in MySQL Shell 8.0.18 do not support user variables passed to columns options.

You can workaround this limitation by ALTER TABLE with missing columns that exists in import data file with proper column type:

alter table names add column (dummy1 integer, dummy2 integer);

import your data e.g. /tmp/names.csv[1]

util.importTable("/tmp/names.csv", {table:"names", columns: ["id", "firstname", "dummy1", "dummy2", "lastname"], dialect: "csv-unix"})

and drop dummy columns:

alter table names drop column dummy1;
alter table names drop column dummy2;

or simply invoke LOAD DATA LOCAL INFILE SQL command:

LOAD DATA LOCAL INFILE '/tmp/sample.csv'
  INTO TABLE myschema.mytable
  (id, firstname, @dummy, @dummy, lastname);

[1] Sample data /tmp/names.csv

1,"Nicole",71,29,"Tusk"
2,"Bob",49,66,"Schiffer"
3,"Susan",61,17,"Tusk"
4,"Bob",24,59,"Trump"
5,"Nicole",25,46,"Goldberg"
6,"Bob",16,71,"Goldberg"
7,"Mark",43,43,"Schiffer"