Class: DataDuck::RedshiftDestination

Inherits:
Destination show all
Defined in:
lib/dataduck/redshift_destination.rb

Instance Attribute Summary collapse

Attributes inherited from Database

#name

Class Method Summary collapse

Instance Method Summary collapse

Methods inherited from Destination

destination, destination_config, load_config!, only_destination

Constructor Details

#initialize(name, config) ⇒ RedshiftDestination

Returns a new instance of RedshiftDestination.



18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
 
# File 'lib/dataduck/redshift_destination.rb', line 18

def initialize(name, config)
  load_value('aws_key', name, config)
  load_value('aws_secret', name, config)
  load_value('s3_bucket', name, config)
  load_value('s3_region', name, config)
  load_value('host', name, config)
  load_value('port', name, config)
  load_value('database', name, config)
  load_value('schema', name, config)
  load_value('username', name, config)
  load_value('password', name, config)

  @redshift_connection = nil

  super
end

Instance Attribute Details

#aws_key ⇒ Object

Returns the value of attribute aws_key.



7
8
9
 
# File 'lib/dataduck/redshift_destination.rb', line 7

def aws_key
  @aws_key
end

#aws_secret ⇒ Object

Returns the value of attribute aws_secret.



8
9
10
 
# File 'lib/dataduck/redshift_destination.rb', line 8

def aws_secret
  @aws_secret
end

#database ⇒ Object

Returns the value of attribute database.



13
14
15
 
# File 'lib/dataduck/redshift_destination.rb', line 13

def database
  @database
end

#host ⇒ Object

Returns the value of attribute host.



11
12
13
 
# File 'lib/dataduck/redshift_destination.rb', line 11

def host
  @host
end

#password ⇒ Object

Returns the value of attribute password.



16
17
18
 
# File 'lib/dataduck/redshift_destination.rb', line 16

def password
  @password
end

#port ⇒ Object

Returns the value of attribute port.



12
13
14
 
# File 'lib/dataduck/redshift_destination.rb', line 12

def port
  @port
end

#s3_bucket ⇒ Object

Returns the value of attribute s3_bucket.



9
10
11
 
# File 'lib/dataduck/redshift_destination.rb', line 9

def s3_bucket
  @s3_bucket
end

#s3_region ⇒ Object

Returns the value of attribute s3_region.



10
11
12
 
# File 'lib/dataduck/redshift_destination.rb', line 10

def s3_region
  @s3_region
end

#schema ⇒ Object

Returns the value of attribute schema.



14
15
16
 
# File 'lib/dataduck/redshift_destination.rb', line 14

def schema
  @schema
end

#username ⇒ Object

Returns the value of attribute username.



15
16
17
 
# File 'lib/dataduck/redshift_destination.rb', line 15

def username
  @username
end

Class Method Details

.value_to_string(value) ⇒ Object 



264
265
266
267
268
269
270
271
272
273
274
275
276
277
 
# File 'lib/dataduck/redshift_destination.rb', line 264

def self.value_to_string(value)
  string_value = ''

  if value.respond_to?(:strftime)
    from_value = value.respond_to?(:utc) ? value.utc : value
    string_value =  from_value.strftime('%Y-%m-%d %H:%M:%S')
  elsif value.respond_to?(:to_s)
    string_value = value.to_s
  end

  string_value.gsub!('"', '""')

  return string_value
end

Instance Method Details

#connection ⇒ Object 



35
36
37
38
39
40
41
 
# File 'lib/dataduck/redshift_destination.rb', line 35

def connection
  @redshift_connection ||= Sequel.connect("redshift://#{ self.username }:#{ self.password }@#{ self.host }:#{ self.port }/#{ self.database }" +
          "?force_standard_strings=f",
      :client_min_messages => '',
      :force_standard_strings => false
  )
end

#copy_query(table, s3_path) ⇒ Object 



43
44
45
46
47
48
49
50
51
52
53
 
# File 'lib/dataduck/redshift_destination.rb', line 43

def copy_query(table, s3_path)
  properties_joined_string = "\"#{ table.output_column_names.join('","') }\""
  query_fragments = []
  query_fragments << "COPY #{ table.staging_name } (#{ properties_joined_string })"
  query_fragments << "FROM '#{ s3_path }'"
  query_fragments << "CREDENTIALS 'aws_access_key_id=#{ self.aws_key };aws_secret_access_key=#{ self.aws_secret }'"
  query_fragments << "REGION '#{ self.s3_region }'"
  query_fragments << "CSV IGNOREHEADER 1 TRUNCATECOLUMNS ACCEPTINVCHARS EMPTYASNULL"
  query_fragments << "DATEFORMAT 'auto'"
  return query_fragments.join(" ")
end

#create_columns_on_data_warehouse!(table) ⇒ Object 



55
56
57
58
59
60
61
62
63
64
 
# File 'lib/dataduck/redshift_destination.rb', line 55

def create_columns_on_data_warehouse!(table)
  columns = get_columns_in_data_warehouse(table.building_name)
  column_names = columns.map { |col| col[:name].to_s }
  table.output_schema.map do |name, data_type|
    if !column_names.include?(name.to_s)
      redshift_data_type = self.type_to_redshift_type(data_type)
      self.query("ALTER TABLE #{ table.building_name } ADD #{ name } #{ redshift_data_type }")
    end
  end
end

#create_output_table_with_name!(table, name) ⇒ Object 



90
91
92
 
# File 'lib/dataduck/redshift_destination.rb', line 90

def create_output_table_with_name!(table, name)
  self.query(self.create_table_query(table, name))
end

#create_output_tables!(table) ⇒ Object 



80
81
82
83
84
85
86
87
88
 
# File 'lib/dataduck/redshift_destination.rb', line 80

def create_output_tables!(table)
  self.create_output_table_with_name!(table, table.building_name)
  self.create_columns_on_data_warehouse!(table)

  if table.building_name != table.staging_name
    self.drop_staging_table!(table)
    self.create_output_table_with_name!(table, table.staging_name)
  end
end

#create_table_query(table, table_name = nil) ⇒ Object 



66
67
68
69
70
71
72
73
74
75
76
77
78
 
# File 'lib/dataduck/redshift_destination.rb', line 66

def create_table_query(table, table_name = nil)
  table_name ||= table.name
  props_array = table.output_schema.map do |name, data_type|
    redshift_data_type = self.type_to_redshift_type(data_type)
    "\"#{ name }\" #{ redshift_data_type }"
  end
  props_string = props_array.join(', ')

  distribution_clause = table.distribution_key ? "DISTKEY(#{ table.distribution_key })" : ""
  index_clause = table.indexes.length > 0 ? "INTERLEAVED SORTKEY (#{ table.indexes.join(',') })" : ""

  "CREATE TABLE IF NOT EXISTS #{ table_name } (#{ props_string }) #{ distribution_clause } #{ index_clause }"
end

#data_as_csv_string(data, property_names) ⇒ Object 



94
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
 
# File 'lib/dataduck/redshift_destination.rb', line 94

def data_as_csv_string(data, property_names)
  data_string_components = [] # join strings this way for now, could be optimized later

  data_string_components << property_names.join(',') # header column
  data_string_components << "\n"

  data.each do |result|
    property_names.each_with_index do |property_name, index|
      value = result[property_name.to_sym]
      if value.nil?
        value = result[property_name.to_s]
      end

      if index == 0
        data_string_components << '"'
      end

      data_string_components << DataDuck::RedshiftDestination.value_to_string(value)

      if index == property_names.length - 1
        data_string_components << '"'
      else
        data_string_components << '","'
      end
    end
    data_string_components << "\n"
  end

  return data_string_components.join
end

#dbconsole(options = {}) ⇒ Object 



139
140
141
142
143
144
145
146
147
148
149
 
# File 'lib/dataduck/redshift_destination.rb', line 139

def dbconsole(options = {})
  args = []
  args << "--host=#{ self.host }"
  args << "--username=#{ self.username }"
  args << "--dbname=#{ self.database }"
  args << "--port=#{ self.port }"

  ENV['PGPASSWORD'] = self.password

  self.find_command_and_execute("psql", *args)
end

#drop_staging_table!(table) ⇒ Object 



151
152
153
 
# File 'lib/dataduck/redshift_destination.rb', line 151

def drop_staging_table!(table)
  self.query("DROP TABLE IF EXISTS #{ table.staging_name }")
end

#finish_fully_reloading_table!(table) ⇒ Object 



224
225
226
227
228
229
230
231
232
233
234
 
# File 'lib/dataduck/redshift_destination.rb', line 224

def finish_fully_reloading_table!(table)
  self.query("DROP TABLE IF EXISTS zz_dataduck_old_#{ table.name }")

  table_already_exists = self.table_names.include?(table.name)
  if table_already_exists
    self.query("ALTER TABLE #{ table.name } RENAME TO zz_dataduck_old_#{ table.name }")
  end

  self.query("ALTER TABLE #{ table.staging_name } RENAME TO #{ table.name }")
  self.query("DROP TABLE IF EXISTS zz_dataduck_old_#{ table.name }")
end

#get_columns_in_data_warehouse(table_name) ⇒ Object 



155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
 
# File 'lib/dataduck/redshift_destination.rb', line 155

def get_columns_in_data_warehouse(table_name)
  cols_query = "SELECT pg_table_def.column AS name, type AS data_type, distkey, sortkey FROM pg_table_def WHERE tablename='#{ table_name }'"
  results = self.query(cols_query)

  columns = []
  results.each do |result|
    columns << {
        name: result[:name],
        data_type: result[:data_type],
        distkey: result[:distkey],
        sortkey: result[:sortkey],
    }
  end

  return columns
end

#load_table!(table) ⇒ Object 



236
237
238
239
240
241
242
243
244
245
246
247
 
# File 'lib/dataduck/redshift_destination.rb', line 236

def load_table!(table)
  DataDuck::Logs.info "Loading table #{ table.name }..."
  s3_object = self.upload_table_to_s3!(table)
  self.create_output_tables!(table)
  self.query(self.copy_query(table, s3_object.s3_path))
  s3_object.delete!

  if table.staging_name != table.building_name
    self.merge_from_staging!(table)
    self.drop_staging_table!(table)
  end
end

#merge_from_staging!(table) ⇒ Object 



172
173
174
175
176
177
178
179
180
181
182
183
184
 
# File 'lib/dataduck/redshift_destination.rb', line 172

def merge_from_staging!(table)
  if table.staging_name == table.building_name
    return
  end

  # Following guidelines in http://docs.aws.amazon.com/redshift/latest/dg/merge-examples.html
  staging_name = table.staging_name
  building_name = table.building_name
  delete_query = "DELETE FROM #{ building_name } USING #{ staging_name } WHERE #{ building_name }.id = #{ staging_name }.id" # TODO allow custom or multiple keys
  self.query(delete_query)
  insert_query = "INSERT INTO #{ building_name } (\"#{ table.output_column_names.join('","') }\") SELECT \"#{ table.output_column_names.join('","') }\" FROM #{ staging_name }"
  self.query(insert_query)
end

#query(sql) ⇒ Object 



186
187
188
189
190
191
192
193
194
195
196
197
 
# File 'lib/dataduck/redshift_destination.rb', line 186

def query(sql)
  Logs.debug("SQL executing on #{ self.name }:\n  " + sql)
  begin
    self.connection[sql].map { |elem| elem }
  rescue Exception => err
    if err.to_s.include?("Check 'stl_load_errors' system table for details")
      self.raise_stl_load_error!
    else
      raise err
    end
  end
end

#raise_stl_load_error! ⇒ Object

Raises:



199
200
201
202
203
204
205
206
 
# File 'lib/dataduck/redshift_destination.rb', line 199

def raise_stl_load_error!
  load_error_sql = "SELECT filename, line_number, colname, position, err_code, err_reason FROM stl_load_errors ORDER BY starttime DESC LIMIT 1"
  load_error_details = self.connection[load_error_sql].map { |elem| elem }.first

  raise RedshiftLoadError.new("Error loading Redshift, '#{ load_error_details[:err_reason].strip }' " +
      "(code #{ load_error_details[:err_code] }) with file #{ load_error_details[:filename].strip } " +
      "for column '#{ load_error_details[:colname].strip }'. The error occurred at line #{ load_error_details[:line_number] }, position #{ load_error_details[:position] }.")
end

#recreate_table!(table) ⇒ Object 



249
250
251
252
253
254
255
256
257
258
259
260
261
262
 
# File 'lib/dataduck/redshift_destination.rb', line 249

def recreate_table!(table)
  DataDuck::Logs.info "Recreating table #{ table.name }..."

  if !self.table_names.include?(table.name)
    raise Exception.new("Table #{ table.name } doesn't exist on the Redshift database, so it can't be recreated. Did you want to use `dataduck create #{ table.name }` instead?")
  end

  recreating_temp_name = "zz_dataduck_recreating_#{ table.name }"
  self.create_output_table_with_name!(table, recreating_temp_name)
  self.query("INSERT INTO #{ recreating_temp_name } (\"#{ table.output_column_names.join('","') }\") SELECT \"#{ table.output_column_names.join('","') }\" FROM #{ table.name }")
  self.query("ALTER TABLE #{ table.name } RENAME TO zz_dataduck_recreating_old_#{ table.name }")
  self.query("ALTER TABLE #{ recreating_temp_name } RENAME TO #{ table.name }")
  self.query("DROP TABLE zz_dataduck_recreating_old_#{ table.name }")
end

#table_names ⇒ Object 



208
209
210
 
# File 'lib/dataduck/redshift_destination.rb', line 208

def table_names
  self.query("SELECT DISTINCT(tablename) AS name FROM pg_table_def WHERE schemaname='public' ORDER BY name").map { |item| item[:name] }
end

#type_to_redshift_type(which_type) ⇒ Object 



125
126
127
128
129
130
131
132
133
134
135
136
137
 
# File 'lib/dataduck/redshift_destination.rb', line 125

def type_to_redshift_type(which_type)
  which_type = which_type.to_s

  if ["string", "text", "bigtext"].include?(which_type)
    {
        "string" => "varchar(255)",
        "text" => "varchar(8191)",
        "bigtext" => "varchar(65535)", # Redshift maximum
    }[which_type]
  else
    which_type
  end
end

#upload_table_to_s3!(table) ⇒ Object 



212
213
214
215
216
217
218
219
220
221
222
 
# File 'lib/dataduck/redshift_destination.rb', line 212

def upload_table_to_s3!(table)
  now_epoch = Time.now.to_i.to_s
  filepath = "pending/#{ table.name.downcase }_#{ now_epoch }.csv"

  table_csv = self.data_as_csv_string(table.data, table.output_column_names)

  s3_obj = S3Object.new(filepath, table_csv, self.aws_key, self.aws_secret,
      self.s3_bucket, self.s3_region)
  s3_obj.upload!
  return s3_obj
end