Class: Table

Inherits:
Object
  • Object
show all
Defined in:
lib/gooddata_marketo/helpers/table.rb

Instance Method Summary collapse

Constructor Details

#initialize(config = {}) ⇒ Table

Returns a new instance of Table.



5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
 
# 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

Instance Method Details

#add_column(column, config = {}) ⇒ Object 



127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
 
# 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) ⇒ Object 



56
57
58
59
60
 
# 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) ⇒ Object 



229
230
231
232
233
234
235
236
237
238
239
240
241
 
# 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 ⇒ Object 



168
169
170
 
# 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) ⇒ Object 



199
200
201
202
203
204
205
206
207
208
209
210
211
 
# 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) ⇒ Object 



185
186
187
188
189
190
191
192
193
194
195
196
197
 
# 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) ⇒ Object 



95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
 
# 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) ⇒ Boolean

Returns:

  • (Boolean) 


27
28
29
30
31
32
33
34
35
36
37
 
# 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) ⇒ Object 



213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
 
# 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) ⇒ Object 



39
40
41
42
43
44
45
46
47
48
49
 
# 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) ⇒ Object 



67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
 
# 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) ⇒ Object 



62
63
64
65
 
# File 'lib/gooddata_marketo/helpers/table.rb', line 62

def log message
  puts "#{Time.now} => #{message}" if GoodDataMarketo.logging
  message
end

#merge_columns(config = {}) ⇒ Object 



243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
 
# 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= ⇒ Object 



319
320
321
 
# File 'lib/gooddata_marketo/helpers/table.rb', line 319

def name=
  @name_of_table
end

#remove_column(column) ⇒ Object Also known as: drop_column 



149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
 
# 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

#rename(new_name) ⇒ Object 



51
52
53
54
 
# 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) ⇒ Object Also known as: query 



172
173
174
175
176
177
178
179
180
181
 
# 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