load_data_infile2

LoadDataInfile2

Import the data at a high speed to the table from a text file, using the MySQL LOAD DATA INFILE statement.

This gem is dependent on mysql2.

By using mysql2, as well as plugin of ActiveRecord, it is possible to use in pure Ruby script.

Installation

Add to your Gemfile:

gem 'load_data_infile2'

And bundle.

Examples

Basic Usage

Database configuration:

db_config = {
  host: 'localhost'
  database: 'ldi_test'
  username: 'root'
}

Create client:

ldi_client = LoadDataInfile2::Client.new(db_config)

Import from CSV file:

ldi_client.import('/path/to/data.csv')

Default options are CSV format:

module LoadDataInfile2
  class << self
    def default_import_options
      @default_import_options ||= {
        fields_terminated_by: ',',          # CSV
        fields_optionally_enclosed_by: '"', # standard format of CSV
        fields_escaped_by: '"',             # standard format of CSV
        lines_terminated_by: "\\n",
        ignore_lines: 0
      }
    end
  end
end

TSV format

If you are using TSV format:

opts = {
  fileds_terminated_by: "\\t",
  fields_optionally_enclosed_by: "",
  fields_escaped_by: "\\"
}
ldi_client = LoadDataInfile2::Client.new(db_config, opts)
ldi_client.import('/path/to/data.tsv')

LOAD DATA LOCAL INFILE

If you use LOCAL option:

opts = { local_infile: true }
ldi_client = LoadDataInfile2::Client.new(db_config, opts)
ldi_client.import('/path/to/data.csv')
# => Execute "LOAD DATA LOCAL INFILE '/path/to/data.csv' INTO TABLE `ldi_test`.`data`;"

SQL Options

Support all options of LOAD DATA INFILE statement on MySQL 5.7 .

see: http://dev.mysql.com/doc/refman/5.7/en/load-data.html

For examples:

opts = { local_infile: true }
sql_opts = { table: 'special_users', ignore_lines: 1 }
ldi_client = LoadDataInfile2::Client.new(db_config, opts)
ldi_client.import('/path/to/users.csv', sql_opts)

Mappings

MySQL	LoadDataInfile2
LOW_PRIORITY	low_priority_or_concurrent: :low_priority
CONCURRENT	low_priority_or_concurrent: :concurrent
LOCAL	local_infile: true
REPLACE	replace_or_ignore: :replace
IGNORE	replace_or_ignore: :ignore
tbl_name	table: 'special_table_name'
PARTITION	partition: 'p0' / ['p0', 'p1', ...]
CHARCTER SET	charset: 'utf8'
FIELDS TERMINATED BY	fields_terminated_by: ','
FIELDS ENCLOSED BY	fields_enclosed_by: '"'
FIELDS OPTIONALLY ENCLOSED BY	fields_optionally_enclosed_by: '"'
FIELDS ESCAPED BY	fields_escaped_by: '"'
LINES STARTING BY	lines_starting_by: '***'
LINES TERMINATED BY	lines_terminated_by: '\n'
IGNORE LINES	ignore_lines: 1
col_name_or_user_var	columns: ['col1', 'col2', '@var3', ...]
SET col_name = expr	set: { col1: "'specific value'", col2: '@var', col3: 'NOW()' }

In Rails

Subclass of ActiveRecord is added .load_data_infile.

For example, in the case of User model, you can call the class method named load_data_infile from the User model.

User.load_data_infile('/path/to/data.csv')

If you want to pass options to the initialization of LoadDataInfile2::ActiveRecord, you can use the accessor of class variable named .default_load_data_infile_options.

User.default_load_data_infile_options = { ignore_lines: 1 }
User.load_data_infile('/path/to/data.csv')

Contributing

Bug reports and pull requests are welcome on GitHub at https://github.com/nalabjp/load_data_infile2.

License

MIT License

The gem is available as open source under the terms of the MIT License.