Creating New dwh_custom Tables and Populating with Data

 It's easy to create a custom table in the NetApp OCI Data Warehouse dwh_custom schema. And fairly common too. Often customers have a spreadsheet of data that they want added to their reports.

Firstly, to create a table with a primary key (the primary key is usually the key you'll be using to join your new table to another table):

CREATE TABLE IF NOT EXISTS dwh_custom.yourtable (
  yourprimarykey varchar(25) NOT NULL PRIMARY KEY,
  yourdata1 varchar(99),
  yourdata2 varchar(99)
);

There are various ways to get the data from an excel spreadssheet. I find the easiest (sometimes CSV upload if fiddly) is to insert the data. So, in your spreadsheet, for each row, you'll have a formula like:

"INSERT IGNORE INTO dwh_custom.yourtable VALUES ('"&A2&"','"&B2&"','"&C2&"');"

Once you've got your data in the data warehouse, you should be able to easily join your new table to existing data.

SELECT * FROM
dwh_inventory.sometable AS s
JOIN dwh_custom.yourtable AS y ON y.yourprimarykey = s.something;

If the data needs to be regularly updated in the dwh_custom, then you'd need to consider a custom integration.