bigdata_migrate_millions.rb

module Scripts::MigrateBigData

	def self.bulk_big_data_to(table_origin,table_destination, date)
	    start_proc = Time.now
	    con_sql = ActiveRecord::Base.connection()
	    if !con_sql.table_exists?(table_destination.to_sym)
		    con_sql.execute("CREATE TABLE #{table_destination} like #{table_origin}")
		    con_sql.execute("alter table #{table_destination} change order_id order_id integer(15)")
        puts "First step remove all index for speed..."
		    con_sql.execute("alter table #{table_destination} drop index index_spree_order_events_on_order_number")
		    con_sql.execute("alter table #{table_destination} drop index index_spree_order_events_on_order_number_and_type")

		    inserts_values,size_values,cont_waste,cont = [], 50, 0, 0
		    columns_origin = "(`order_id`, `origin_type`, `origin_id`, `kind`, `data`, `created_at`, `updated_at`)"
		    OrderEvent.find_each(batch_size: 50) do |oe|
				if oe.order_id.to_i != 0 && date >= oe.created_at.strftime("%F")
					kind = oe.kind.nil? ? '' : oe.kind
					s = oe.data.nil? ? ''.inspect : oe.data.inspect

					if oe.origin_id.nil?
						inserts_values.push "(#{oe.order_id.to_i}, '#{oe.origin_type}', null, '#{kind}', #{s}, '#{oe.created_at.to_s(:db)}', '#{oe.updated_at.to_s(:db)}')"
					else
						inserts_values.push "(#{oe.order_id.to_i}, '#{oe.origin_type}', #{oe.origin_id.to_i}, '#{kind}', #{s}, '#{oe.created_at.to_s(:db)}', '#{oe.updated_at.to_s(:db)}')"
					end
					if inserts_values.size >= size_values
					  sql_insert = "INSERT INTO #{table_destination} #{columns_origin} 
					  				VALUES #{inserts_values.join(", ")}"
					  begin	
					  	con_sql.execute sql_insert
					  rescue Exception => e
					  	puts "No save ERROR #{e}" 
					  end
					  inserts_values = []
					end
				else
					cont_waste = cont_waste + 1
				end
			end

			if inserts_values.size > 0
				sql_insert = "INSERT INTO #{table_destination} #{columns_origin}
							  VALUES #{inserts_values.join(", ")}"
			    con_sql.execute sql_insert
			end
			
			con_sql.execute "rename table #{table_origin} to #{table_origin}_history"
			con_sql.execute "rename table #{table_destination} to #{table_origin}"

		    end_proc = Time.now - start_proc
		    time_total = Time.at(end_proc.to_i.abs).utc.strftime "%H:%M:%S"
		    puts "End Process #{time_total}"
		    puts "Create Index.."
			start_proc = Time.now
		    con_sql.execute "alter table #{table_origin} add index index_spree_order_events_on_order_number(order_id)"
		    con_sql.execute "alter table #{table_origin} add index index_spree_order_events_on_order_number_and_type(order_id,kind)"
			end_proc = Time.now - start_proc
			time_total = Time.at(end_proc.to_i.abs).utc.strftime "%H:%M:%S"
			puts "time to create Index..#{time_total}"
		else
			puts "Warning!!. Please remove destination table #{table_destination}"
		end
	end
end