CODEWITHSUNDEEP
rubyamazon-redshiftsequel
Sixty4Bit
Sixty4Bit

Reputation: 13402

Insert into select with a hard coded value with Ruby Sequel

I am using Ruby Sequel to move data into reporting tables daily. I am consolidating data from three tables into a single table. I am doing this in Redshift so I have to use disable_insert_returning. The names of the columns in the two tables match each other but not the end table which means I am using graph and set_graph_aliases. 

reports = db[:reports]
report_columns = [:user_id, :purchase_date, :sku]

spoons_select_graph = {
  user_id: :users,
  purchase_date: :spoon_receipts,
  product_id: :spoon_receipts
}
spoons = db[:spoon_receipts]
spoons_select = spoons.graph(:users, user_id: :user_id).set_graph_aliases(spoons_select_graph)

forks_select_graph = {
  user_id: :users,
  purchase_date: :fork_receipts,
  product_id: :fork_receipts
}
forks = db[:fork_receipts]
forks_select = forks.graph(:users, user_id: :user_id).set_graph_aliases(forks_select_graph)

reports.disable_insert_returning.insert(report_columns, spoons_select)
reports.where(channel: nil).update(channel: 'spoons')
reports.disable_insert_returning.insert(report_columns, forks_select)
reports.where(channel: nil).update(channel: 'forks')

The updates are taking forever. What I would like to do is add the channel to the insert select so that I don't have to go back and update.

Upvotes: 0

Views: 311

Answers (1)

Old Pro
Old Pro

Reputation: 25557

You didn't provide an executable setup, so I haven't tested this, but I think it will work. The basic idea is to just add a constant column of results to your select.

reports = db[:reports]
report_columns = [:user_id, :purchase_date, :sku, :channel]

spoons_select_graph = {
  user_id: :users,
  purchase_date: :spoon_receipts,
  product_id: :spoon_receipts,
  channel: [:spoon_receipts, :channel, 'spoons']
}
spoons = db[:spoon_receipts]
spoons_select = spoons.graph(:users, user_id: :user_id).set_graph_aliases(spoons_select_graph)

reports.disable_insert_returning.insert(report_columns, spoons_select)

See the documentation for set_graph_aliases for more information.

Upvotes: 1

Related Questions