Hi there
I have been trying for days to get bulk import working with
Activewarehouse-ETL
Here is my rule in employees.ctl:
post_process :bulk_import, {
:file => 'output/employees.out.txt',
:columns => EMPLOYEE_COLS,
:truncate => true,
:field_separator => ',',
:field_enclosure => '"',
#:line_separator => '\n',
:ignore => 1,
:target => :development,
:table => 'employees'
}
====This is the error I get when running:====
> C:\Documents and Settings\Administrator\My
> Documents\rails\ourpeople\import>etl -c ../config/database.yml --limit 200
> employees.ctl
> Using AdapterExtensions
> Starting ETL process
> initializing ETL engine
> Processing employees.ctl
> Source: employees.csv
> Limiting enabled: 200
> Reached limit of 200
>
> Executing screens
> Screens passed
>
> Executing post processes
> c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/connection_adapters/abstract_adapter.rb:150:in
> `log': Mysql::Error: #42000The used command is not allowed with this MySQL
> version: LOAD DATA LOCAL INFILE 'C:/Documents and Settings/Administrator/My
> Documents/rails/ourpeople/import/output/employees.out.txt' INTO TABLE
> employees FIELDS TERMINATED BY ',' ENCLOSED BY '"' IGNORE 1 LINES
> (id,cgid,longid,firstname,lastname,displayname,middleinitial,initials,phone,mobile,fax,subcellphone,email,englishtitle,localtitle,secretary_id,mentor_id,substitute_id,manager_id,comment,licenseplate,employment_cgemployee,employment_cgexternal,employment_cgtemporary,employeework_employeeid,employeework_workpercentage,employeework_actualworkpercentage,company_id,grade_id,location_id,organisation_id,profession_id,resourcetype_id,skillsunit_id,staffingunit_id)
> (ActiveRecord::StatementInvalid)
> from
> c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/connection_adapters/mysql_adapter.rb:281:in
> `execute'
> from
> c:/ruby/lib/ruby/gems/1.8/gems/adapter_extensions-0.4.0/lib/adapter_extensions/connection_adapters/mysql_adapter.rb:46:in
> `do_bulk_load'
> from
> c:/ruby/lib/ruby/gems/1.8/gems/adapter_extensions-0.4.0/lib/adapter_extensions/connection_adapters/abstract_adapter.rb:18:in
> `bulk_load'
> from
> c:/ruby/lib/ruby/gems/1.8/gems/activewarehouse-etl-0.9.0/bin/../lib/etl/processor/bulk_import_processor.rb:73:in
> `process'
> from
> c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/connection_adapters/abstract/database_statements.rb:66:in
> `transaction'
> from
> c:/ruby/lib/ruby/gems/1.8/gems/activewarehouse-etl-0.9.0/bin/../lib/etl/processor/bulk_import_processor.rb:62:in
> `process'
> from
> c:/ruby/lib/ruby/gems/1.8/gems/activewarehouse-etl-0.9.0/bin/../lib/etl/engine.rb:485:in
> `post_process'
> from
> c:/ruby/lib/ruby/gems/1.8/gems/activewarehouse-etl-0.9.0/bin/../lib/etl/engine.rb:484:in
> `each'
> ... 13 levels...
> from
> c:/ruby/lib/ruby/gems/1.8/gems/activesupport-2.0.2/lib/active_support/dependencies.rb:496:in
> `require'
> from c:/ruby/lib/ruby/gems/1.8/gems/activewarehouse-etl-0.9.0
> /bin/etl:28
> from c:/ruby/bin/etl:19:in `load'
> from c:/ruby/bin/etl:19
>
====end of error====
I have tried a lot of different combinations of flags in that bulk_import
directive. Am i getting it right? Could you please show me some working
examples.
Also, for adapter_extensions there seems to be a patch adding this line to
the mysql adapter
> --- activerecord/lib/active_record/connection_adapters/mysql_adapter.rb
> (revision 8484)
> +++ activerecord/lib/active_record/connection_adapters/mysql_adapter.rb
> (working copy)
> @@ -467,6 +467,7 @@
> if encoding
> @connection.options(Mysql::SET_CHARSET_NAME, encoding) rescue
> nil
> end
> + @connection.options(Mysql::OPT_LOCAL_INFILE, true)
> @connection.ssl_set(@config[:sslkey], @config[:sslcert],
> @config[:sslca], @config[:sslcapath], @config[:sslcipher]) if
> @config[:sslkey]
> @connection.real_connect([EMAIL PROTECTED])
> execute("SET NAMES '#{encoding}'") if encoding
>
Well, i did not apply the patch to version 0.4.0 because it seemed like it
was already in the code (however, not "true" but "1" at the
end of the line)
The most odd thing is that I can easily run a bulk import via the local sql
command:
> /ourpeople/import/>
> $ mysql ourpeople_development < import.sql --user=root -p
>
Where the import.sql contains
> LOAD DATA LOCAL INFILE './output/employees.out.txt' INTO TABLE
> employees CHARACTER SET latin1 FIELDS TERMINATED BY ',' IGNORE 1 LINES
> ;
Any suggestions/ideas to what i might be doing wrong? Any examples of
working code you can share?
--
___________________________________________________________
Jesper Rønn-Jensen / Capgemini Denmark
Tel. +45 3977 8220 / Mob. +45 2373 6220 / Fax +45 7011 2201
www.dk.capgemini.com / Blog http://justaddwater.dk/
[EMAIL PROTECTED] (Private e-mail and Google Talk IM)
___________________________________________________________
_______________________________________________
Activewarehouse-discuss mailing list
Activewarehouse-discuss@rubyforge.org
http://rubyforge.org/mailman/listinfo/activewarehouse-discuss
