Use cases of command-line options

This topic describes use cases of command-line options in a complex scenario.

--auto-column-mapping

By default, OBLOADER requires that the columns in the source file exactly match those of the target table. OBLOADER imports data to the target columns in order without identifying column names.

If the number of columns in the source file is different from that of the target table, you can specify the --auto-column-mapping option to define column name mappings, so that OBLOADER imports data based on the column names, and ignores columns that have no matching columns in the target table.

Scenarios

Assume that the CSV file containing the col1, col2, and col3 columns of the my_table table in the /home/admin/obloader directory needs to be imported into the target table containing the col2, col3, and col4 columns in the obloader database.

• Data of the source table

  $cat my_table.csv
  'col1','col2','col3'
  'data1',1,'desc1'
  'data2',2,'desc2'
  'data3',3,'desc3'
  

• Schema of the target table

  obclient [obloader]> DESC my_table;
  +-------+--------------+------+------+---------+-------+
  | Field | Type         | Null | Key  | Default | Extra |
  +-------+--------------+------+------+---------+-------+
  | col2  | int(11) | YES  |      | NULL    |       |
  | col3  | varchar(255)     | YES  |      | NULL    |       |
  | col4  | varchar(255) | YES  |      | NULL    |       |
  +-------+--------------+------+------+---------+-------+
  3 rows in set (0.035 sec)
  

Import data without specifying --auto-column-mapping

1. Run the following command to import data:

   ./obloader -h xxx.xxx.xxx.xxx -P xxxx -u test@mysql -D obloader --csv --table 'my_table' -f /home/admin/obloader
   

2. Wait for the execution result. An error is expected.

   Notice

   If the column names do not match but the column types do, OBLOADER may ignore the column names and import the data, leading to data inconsistency.

3. Query the data in the target table.

   SELECT * FROM my_table;
   

   The query result is as follows, indicating that the import failed:

   obclient [obloader]> SELECT * FROM my_table;
   Empty set (0.035 sec)
   

Import data with --auto-column-mapping specified

1. Run the following command to import data:

   ./obloader -h xxx.xxx.xxx.xxx -P xxxx -u test@mysql -D obloader --csv --table 'my_table' --auto-column-mapping -f /home/admin/obloader
   

2. Wait for the execution result. The expected result is that data of the col2 and col3 columns of the source table is imported, and the col4 column is filled with default values.

3. Query the data in the target table.

   SELECT * FROM my_table;
   

   The query result, as expected, is as follows:

   obclient [obloader]> SELECT * FROM my_table;
   +------+---------+------+
   | col2 | col3    | col4 |
   +------+---------+------+
   |    1 | desc1   | NULL |
   |    2 | desc2   | NULL |
   |    3 | desc3   | NULL |
   +------+---------+------+
   3 rows in set (0.033 sec)