Rebuild OCI DWH on A Fresh MySQL DB Instance: 1 & 2: Re-Creating Schemas and Tables

on

For the purposes of having an "on-premise" copy of the CI DWH, we need to re-create OCI DWH schemas, tables, views, stored procedures, etcetera ... on another MySQL Database Instance (which might even be AWS MySQL.) Then we can use the Cloud Insights REST ODATA APIs to get the table data, do a little bit of transformation to the data, and insert the data into the tables. And - voila - we have an "on-premise" copy of our CI DWH. Since it is easiest to get the 'create database', 'create table', 'create view' commands from the OCI DWH (and they should be very close to CI DWH), this is why we use the OCI DWH.

Note: We don't simply backup and restore from the OCI DWH, because things will have different IDs in OCI to what they have in CI. We just want to create the schemas, tables, views, and anything else, and then populate it with data (using CI's REST ODATA API.) But ... maybe that would have been a better approach and just truncate all the tables and set auto_increments to 1. If you want to try the truncate method, this is pretty much all you need to know: How To Copy a MySQL Database (mysqltutorial.org)

1) Re-Creating the Schemas

Acquire the output of -

show create schema SCHEMA_NAME;

- for every schema and run these commands.

2) Re-Creating the Tables

This is more challenging because you might have a lot of tables. I'm using dwh_custom here, and have highlighted anything you might want to modify for your own purposes.

1) First to get the show create table commands running MySQL from the command line:

set sqlquery="SELECT CONCAT('show create table ',TABLE_SCHEMA,'.',TABLE_NAME,';') FROM information_schema.tables WHERE TABLE_SCHEMA = 'dwh_custom' AND TABLE_TYPE = 'BASE TABLE'"

set outfile=Z:\dwh_custom.get_show_create_tables.txt

And run from the command prompt (might need to run from where your MySQL.exe is, if you don't have the path configured)>

MYSQL -uUSERNAME -pPASSWORD -hHOST -e %sqlquery% > %outfile%

2) Then get the create table outputs:

set outfile=Z:\dwh_custom.get_create_tables.txt

Basically, you need to wrap the output from the 1 (each line) with:

MYSQL -uUSERNAME -pPASSWORD -hHOST -e "SHOW CREATE TABLE..." >> %outfile%

  • TIP 1: Use Notepad++ to add the first part
  • TIP 2: Do a replace all on ; to add the end part

And copy and paste everything into the command prompt.

3) Creating the tables:

The output from 2 needs a bit of parsing.

  1. Replace the line 'Table    Create Table' with nothing
  2. Replace all the \n with nothing
  3. Get rid of everything before CREATE TABLE
  4. Replace "CREATE TABLE " with "CREATE TABLE IF NOT EXISTS dwh_custom."
  5. Put ";" on the end
  6. Delete AUTO_INCREMENT=XXXXX (it will reinitialize back to 1 once you start inserting data) (An easier solution would just be to reset the AUTO_INCREMENT back to 1: ALTER TABLE table_name AUTO_INCREMENT = 1;)
  7. Look out for FOREIGN KEY. If a 'create table' has a REFERENCES, the referenced table needs to be created first! (Or you could just keep reapplying the table creates until you've reached the correct number. If a table refers to another schema - which is rare but does happen - that table will not be possible to create until after you've created the table in the other schema.)
  8. Run the content in MySQL to create all the tables

Note: This can be done mostly with the simple PowerShell function (actually two functions) below, except for setting the AUTO_INCREMENT back to 1. Paste the sub-function and main-function into your PowerShell command line, and simply run it as:

PS> ProcessCreateTables FILEPATH SCHEMANAME | out-file output.txt

FUNCTION ProcessLine{

  $L = $Args[0]; $S = $Args[1]

  If($L.StartsWith("Table")){RETURN $NULL}

  $L = $L.replace("\n","")

  $L = $L.split("`t")[1]

  $L = $L.replace("CREATE TABLE ","CREATE TABLE IF NOT EXISTS $S.")

  $L = $L + ";"

  RETURN $L       

}

FUNCTION ProcessCreateTables{

  Param(

    [Parameter(Mandatory)][String]$FilePath,

    [Parameter(Mandatory)][String]$SchemaName

  )

  IF(!(Test-Path $FilePath)){RETURN "$FilePath does not exist - exiting!"}

  [System.Array]$FileContent = Get-Content $FilePath

  [System.Array]$NewContent = @()

  $FileContent | FOREACH{

    $NewContent += (ProcessLine $_ $SchemaName)

  }

  RETURN $NewContent

}

Tables created!

Note: I actually ran this for 80 tables in a dwh_custom and had two errors. The two errors were both:

ERROR 1215 (HY000): Cannot add foreign key constraint

And it simply means that the table you wanted to add, couldn't be added at that time because the table with the referred to foreign key didn't yet exist. Re-run the CREATE TABLE command and it should just work. I added this as point 7 above.

Since we're just making a copy, arguably we don't need these foreign key constraints.

3) Re-Creating the Views

See the next post...

Comments

Post a Comment