class Table
Public Class Methods
new(config = {})
click to toggle source
# File lib/gooddata_marketo/helpers/table.rb, line 5 def initialize config = {} @name_of_table = config[:table] || config[:name] @inserts_to_object_count = 0 @checked_columns_this_insert = false @dwh = config[:client] table_exists = @dwh.table_exists? @name_of_table raise 'A client is required for this module to initialize (:client => ???)' unless @dwh # Check if the table exists, if not, create one parsed_columns = self.columns_array_to_sql config[:columns] begin self.create_table parsed_columns rescue puts "#{Time.now} => #{@name_of_table} (Table) exists." if GoodDataMarketo.logging end end
Public Instance Methods
add_column(column, config = {})
click to toggle source
# File lib/gooddata_marketo/helpers/table.rb, line 127 def add_column column, config = {} # EXAMPLE column = 'column_name' type = config[:type] || 'VARCHAR(255)' query = "ALTER TABLE #{@name_of_table} ADD COLUMN #{column} #{type}" puts "#{Time.now} => ADS:Query: #{query}" tries = 3 begin @dwh.execute(query) rescue Exception => exp tries -= 1 if tries > 0 sleep 3 retry else puts exp if GoodDataMarketo.logging end end end
bulk_insert(file_path)
click to toggle source
# File lib/gooddata_marketo/helpers/table.rb, line 56 def bulk_insert file_path loc = Dir.pwd+"/"+file_path query = "COPY #{@name_of_table} FROM LOCAL '#{file_path}' DELIMITER ','" @dwh.execute(query) end
check_for_sql_parse_errors(text)
click to toggle source
# File lib/gooddata_marketo/helpers/table.rb, line 229 def check_for_sql_parse_errors text list = ReservedSqlKeywords.new keywords = list.values if keywords.include? text.upcase puts "#{Time.now} => WARNING: Updated column key \"#{text}\" to \"#{text}\" as it is a SQL reserved keyword." if GoodDataMarketo.logging text="#{text}_m" else text end end
columns()
click to toggle source
# File lib/gooddata_marketo/helpers/table.rb, line 168 def columns @dwh.get_columns(@name_of_table).map { |column| column[:column_name] } end
columns_array_to_sql(columns_array)
click to toggle source
# File lib/gooddata_marketo/helpers/table.rb, line 199 def columns_array_to_sql columns_array c = [] columns_array.each {|a| c << a } id = "#{c.shift.downcase} VARCHAR(255)" columns = c.map { |column| "#{column.gsub(' ','').downcase} VARCHAR(255)" } res = columns.unshift(id) res.join(', ') end
columns_array_to_string(columns_string_array)
click to toggle source
# File lib/gooddata_marketo/helpers/table.rb, line 185 def columns_array_to_string columns_string_array c = [] columns_string_array.each {|a| c << a } id = "#{c.shift.downcase}" self.log columns = c.map { |column| "#{column.gsub(' ','').downcase}" } res = columns.unshift(id) res.join(', ') end
create_table(sql_columns_string)
click to toggle source
# File lib/gooddata_marketo/helpers/table.rb, line 95 def create_table sql_columns_string sql_columns_string = "#{sql_columns_string}" # EXAMPLE sql_columns_string = "id INTEGER PRIMARY KEY, name_first VARCHAR(255), name_last VARCHAR(255)) ORDER BY id SEGMENTED BY HASH(id) ALL NODES" query = "CREATE TABLE #{@name_of_table} (#{sql_columns_string}) ORDER BY id SEGMENTED BY HASH(id) ALL NODES" puts "#{Time.now} => ADS:#{query}" if GoodDataMarketo.logging columns = sql_columns_string.split(', ') columns.map! { |column| s = column.split(' ') case s[0] when 'sys_capture_date' then s[1] = 'DATETIME' when 'activity_date_time' then s[1] = 'DATETIME' else s[1] = 'VARCHAR(255)' end { :column_name => s[0], :data_type => s[1] } } puts "#{Time.now} => ADS:CreateTable:#{query}" if GoodDataMarketo.logging tries = 3 begin @dwh.create_table(@name_of_table, columns) rescue Exception => exp puts exp if GoodDataMarketo.logging end end
exists?(table=nil)
click to toggle source
# File lib/gooddata_marketo/helpers/table.rb, line 27 def exists? table=nil if table query = table else query = @name_of_table end @dwh.table_exists?(query) end
export_to_csv(file_path)
click to toggle source
# File lib/gooddata_marketo/helpers/table.rb, line 213 def export_to_csv file_path tries = 3 begin @dwh.export_to_csv @name_of_table, file_path rescue Exception => exp tries -= 1 if tries > 0 sleep 3 retry else puts exp if GoodDataMarketo.logging end end end
import_csv(file_path)
click to toggle source
# File lib/gooddata_marketo/helpers/table.rb, line 39 def import_csv file_path puts "#{Time.now} => Loading CSV #{file_path} into ADS." if GoodDataMarketo.logging begin @dwh.load_data_from_csv(@name_of_table, file_path) true rescue Exception => exp puts exp if GoodDataMarketo.logging end #@dwh.csv_to_new_table(@name_of_table, file_path) end
insert(object)
click to toggle source
# File lib/gooddata_marketo/helpers/table.rb, line 67 def insert object row = object.to_row.map {|m| escaped = m.gsub("'","''") m = "'#{escaped}'" } row[0].to_i values = row.join(",") columns = self.columns_array_to_string(object.headers) self.log query = "INSERT INTO #{@name_of_table} (#{columns}) VALUES (#{values})" tries = 3 begin @dwh.execute(query) rescue DataLibraryFailureException => e tries -= 1 if tries > 0 sleep 3 retry else puts e if GoodDataMarketo.logging end end @checked_columns_this_insert = false end
log(message)
click to toggle source
# File lib/gooddata_marketo/helpers/table.rb, line 62 def log message puts "#{Time.now} => #{message}" if GoodDataMarketo.logging message end
merge_columns(config = {})
click to toggle source
# File lib/gooddata_marketo/helpers/table.rb, line 243 def merge_columns config = {} columns = config[:merge_with] || config[:columns] @checked_columns_this_insert = true # Set up Leads Table # Check the table columns current_columns = [] if @cached_columns current_columns = @cached_columns else @dwh.get_columns(@name_of_table).each do |object| current_columns << object[:column_name] end @cached_columns = current_columns end proposed_columns = columns.map do |column| column.gsub(' ','').downcase end identical_columns = proposed_columns & current_columns == proposed_columns if identical_columns self.log proposed_columns else # Find the columns that are not in the current columns and add them. update_column_queue = Array.new proposed_columns.pmap do |check_column| if current_columns.find_index(check_column) next # Column else puts "#{Time.now} => Adding new column:#{@name_of_table}:#{check_column}" if GoodDataMarketo.logging check_column = self.check_for_sql_parse_errors(check_column) @cached_columns << check_column # Add the column to the cache @cached_columns.uniq! type = 'VARCHAR(255)' self.add_column(check_column, :type => type) end end puts "#{Time.now} => Table #{@name_of_table} Merge Complete." # If config BOOL = true also removed columns not found in the proposed columns in the warehouse if config[:two_way] || config[:sync] current_columns.each do |check_current_column| if proposed_columns.find_index(check_current_column) next # Column else self.log "#{Time.now} => TABLE:#{@name_of_table} + new column:#{check_current_column}" self.remove_column(check_current_column) end end end end end
name=()
click to toggle source
# File lib/gooddata_marketo/helpers/table.rb, line 319 def name= @name_of_table end
remove_column(column)
click to toggle source
# File lib/gooddata_marketo/helpers/table.rb, line 149 def remove_column column # EXAMPLE column = 'column_name' self.log query = "ALTER TABLE #{@name_of_table} DROP COLUMN #{column}" tries = 3 begin @dwh.execute(query) rescue Exception => exp tries -= 1 if tries > 0 sleep 3 retry else puts exp if GoodDataMarketo.logging end end end
Also aliased as: drop_column
rename(new_name)
click to toggle source
# File lib/gooddata_marketo/helpers/table.rb, line 51 def rename new_name query = "ALTER TABLE #{@name_of_table} RENAME TO #{new_name}" @dwh.execute(query) end
select(command)
click to toggle source
# File lib/gooddata_marketo/helpers/table.rb, line 172 def select command rows = [] @dwh.execute_select(command) do |row| puts row rows << row end self.log rows end
Also aliased as: query