Striim Migration Service for Google Cloud Documentation

Create target tables using SMSGC's schema conversion utility

SMSGC requires tables in the target that match those in the source. You may use any tool you wish to create the target tables.

When the source and target are the same type, you may use native utilities. For example, when going from on-premise PostgreSQL to Google Cloud SQL for PostgreSQL, you could use pg_dump to export the source tables, then Cloud SQL's Import tool to create identical tables in the target. In this case, you will not need to create an initial load application, but you will need to Create the checkpoint table. See rhw SMSGC How-To Guides for detailed recommendations.

If you wish to use SMSGC's schema conversion utility, follow these instructions. Use Google's Cloud Shell to run the script. (You must Connect SMSGC with your private network if the source database is not reachable by SMSGC.)

The following example uses Oracle's HR sample schema:

/opt/striim/bin/schemaConversionUtility.sh \
  -s="oracle" \
  -d="jdbc:oracle:thin:@//<IP address>:<port>/<SID or service name>" \
  -u="<user name>" \
  -p="<password>" \
  -b="HR.%" \
  -t="postgres"

That will generate three files, conversion_report.txt, converted_tables.sql, and converted_foreign_keys.sql. The output will look something like:

SCHEMA CONVERSION RESULTS - 
Schema name - HR
Number of compatible tables - 7
Number of tables compatible with Striim Intelligence - 0
Number of incompatible tables - 0
Number of successful foreign keys - 10
Number of failed foreign keys - 0
The resultant output SQL files, and the report of the schema conversion are located at the folder: ... 

The output folder will contain three files: conversion_report.txt, converted_tables.sql, and converted_tables_with_striim_intelligence.sql.

The schema conversion report tells you that all tables and foreign keys were converted successfully:

SCHEMA CONVERSION REPORT
Generated at: 2020-07-15 07:38:43-0700

Topology: oracle -> postgres

Table Conversion Score:
    - Tables converted successfully: 100.0%
    - Tables converted successfully with Striim Intelligence: 0.0%
    - Tables not converted: 0.0%

Table list - Successful Conversion (7)
    - HR.LOCATIONS
    - HR.COUNTRIES
    - HR.EMPLOYEES
    - HR.JOBS
    - HR.JOB_HISTORY
    - HR.REGIONS
    - HR.DEPARTMENTS

Table list - Successful Converted with Striim Intelligence (0)

Table list - Failed Conversion (0)

Foreign key list - Successful Conversion (10)
    - EMP_JOB_FK
        - Parent Table: HR.EMPLOYEES
        - Reference Table: HR.JOBS
    - DEPT_LOC_FK
        - Parent Table: HR.DEPARTMENTS
        - Reference Table: HR.LOCATIONS
    - DEPT_MGR_FK
        - Parent Table: HR.DEPARTMENTS
        - Reference Table: HR.EMPLOYEES
    - EMP_DEPT_FK
        - Parent Table: HR.EMPLOYEES
        - Reference Table: HR.DEPARTMENTS
    - LOC_C_ID_FK
        - Parent Table: HR.LOCATIONS
        - Reference Table: HR.COUNTRIES
    - JHIST_EMP_FK
        - Parent Table: HR.JOB_HISTORY
        - Reference Table: HR.EMPLOYEES
    - JHIST_JOB_FK
        - Parent Table: HR.JOB_HISTORY
        - Reference Table: HR.JOBS
    - COUNTR_REG_FK
        - Parent Table: HR.COUNTRIES
        - Reference Table: HR.REGIONS
    - JHIST_DEPT_FK
        - Parent Table: HR.JOB_HISTORY
        - Reference Table: HR.DEPARTMENTS
    - EMP_MANAGER_FK
        - Parent Table: HR.EMPLOYEES
        - Reference Table: HR.EMPLOYEES

Foreign key list - Failed Conversion (0)

converted_tables.sql contains:

-- SCHEMA NAME - HR
-- TABLE NAME - HR.LOCATIONS
CREATE TABLE HR.LOCATIONS(LOCATION_ID numeric(4,0) NOT NULL,STREET_ADDRESS character varying(40),
POSTAL_CODE character varying(12),CITY character varying(30) NOT NULL,
STATE_PROVINCE character varying(25),COUNTRY_ID character(2),PRIMARY KEY(LOCATION_ID));
-- TABLE NAME - HR.COUNTRIES
CREATE TABLE HR.COUNTRIES(COUNTRY_ID character(2) NOT NULL,COUNTRY_NAME character varying(40),
REGION_ID numeric,PRIMARY KEY(COUNTRY_ID));
-- TABLE NAME - HR.EMPLOYEES
CREATE TABLE HR.EMPLOYEES(EMPLOYEE_ID numeric(6,0) NOT NULL,FIRST_NAME character varying(20),
LAST_NAME character varying(25) NOT NULL,EMAIL character varying(25) NOT NULL,
PHONE_NUMBER character varying(20),HIRE_DATE date NOT NULL,JOB_ID character varying(10) NOT NULL,
SALARY numeric(8,2),COMMISSION_PCT numeric(2,2),MANAGER_ID numeric(6,0),DEPARTMENT_ID numeric(4,0),
UNIQUE(EMAIL),PRIMARY KEY(EMPLOYEE_ID));
-- TABLE NAME - HR.JOBS
CREATE TABLE HR.JOBS(JOB_ID character varying(10) NOT NULL,JOB_TITLE character varying(35) NOT NULL,
MIN_SALARY numeric(6,0),MAX_SALARY numeric(6,0),PRIMARY KEY(JOB_ID));
-- TABLE NAME - HR.JOB_HISTORY
CREATE TABLE HR.JOB_HISTORY(EMPLOYEE_ID numeric(6,0) NOT NULL,START_DATE date NOT NULL,
END_DATE date NOT NULL,JOB_ID character varying(10) NOT NULL,DEPARTMENT_ID numeric(4,0),
PRIMARY KEY(EMPLOYEE_ID,START_DATE));
-- TABLE NAME - HR.REGIONS
CREATE TABLE HR.REGIONS(REGION_ID numeric NOT NULL,REGION_NAME character varying(25),
PRIMARY KEY(REGION_ID));
-- TABLE NAME - HR.DEPARTMENTS
CREATE TABLE HR.DEPARTMENTS(DEPARTMENT_ID numeric(4,0) NOT NULL,
DEPARTMENT_NAME character varying(30) NOT NULL,MANAGER_ID numeric(6,0),LOCATION_ID numeric(4,0),
PRIMARY KEY(DEPARTMENT_ID));

Caution

The schema conversion utility will move foreign key constraints to a separate script to be run after initial load. If converted_tables.sql contains any other constraints, you must remove them before running the script on the target database, then add the constraints to the target tables after performing the initial load.

When you run the schema conversion utility on your own database, the output may also include two additional files. converted_tables_with_striim_intelligence.sql includes comments regarding mappings that you should review to make sure they are appropriate. For example:

-- SCHEMA NAME - OE2
-- TABLE NAME - OE2.PRODUCT_INFORMATION
-- Target data type character varying might not be the best fit for the incoming data type 
INTERVAL YEAR TO MONTH of the column WARRANTY_PERIOD.
CREATE TABLE OE2.PRODUCT_INFORMATION(PRODUCT_ID numeric(6,0) NOT NULL,
PRODUCT_NAME character varying(50), PRODUCT_DESCRIPTION character varying(2000),
CATEGORY_ID numeric(2,0), WEIGHT_CLASS numeric(1,0), WARRANTY_PERIOD character varying,
SUPPLIER_ID numeric(6,0), PRODUCT_STATUS character varying(20),LIST_PRICE numeric(8,2),
MIN_PRICE numeric(8,2), CATALOG_URL character varying(50), PRIMARY KEY(PRODUCT_ID));

After making any necessary corrections, add these commands to converted_tables.sql.

conversion_failed_tables.sql includes DDL for tables for which SMSGC could not map one or more data types. For example:

-- SCHEMA NAME - OE2
-- TABLE NAME - OE2.PROMOTIONS
-- Source data type ROWID of the column PROMO_ID is not supported, defaulting to ROWID_STRIIM_UNKNOWN
CREATE TABLE OE2.PROMOTIONS(PROMO_ID ROWID_STRIIM_UNKNOWN NOT NULL, PROMO_NAME character varying(20),
PRIMARY KEY(PROMO_ID));

After making the necessary corrections, add these commands to converted_tables.sql. If you need assistance with migrating tables that failed conversion, Contact SMSGC support.

After adding any additional commands from converted_tables_with_striim_intelligence.sql and conversion_failed_tables.sql, run converted_tables.sql on the target database before running the initial load application. With some targets, you may need a third--party client to run the script.

When the schema conversion utility is run on the HR sample schema, converted_foreign_keys.sql contains:

-- SCHEMA NAME - HR
-- CONSTRAINT NAME - EMP_JOB_FK  PARENT TABLE - HR.EMPLOYEES  REFERENCE TABLE - HR.JOBS
ALTER TABLE HR.EMPLOYEES ADD CONSTRAINT EMP_JOB_FK FOREIGN KEY(JOB_ID) REFERENCES HR.JOBS(JOB_ID);
-- CONSTRAINT NAME - DEPT_LOC_FK  PARENT TABLE - HR.DEPARTMENTS  REFERENCE TABLE - HR.LOCATIONS
ALTER TABLE HR.DEPARTMENTS ADD CONSTRAINT DEPT_LOC_FK FOREIGN KEY(LOCATION_ID) 
REFERENCES HR.LOCATIONS(LOCATION_ID);
-- CONSTRAINT NAME - DEPT_MGR_FK  PARENT TABLE - HR.DEPARTMENTS  REFERENCE TABLE - HR.EMPLOYEES
ALTER TABLE HR.DEPARTMENTS ADD CONSTRAINT DEPT_MGR_FK FOREIGN KEY(MANAGER_ID) 
REFERENCES HR.EMPLOYEES(EMPLOYEE_ID);
-- CONSTRAINT NAME - EMP_DEPT_FK  PARENT TABLE - HR.EMPLOYEES  REFERENCE TABLE - HR.DEPARTMENTS
ALTER TABLE HR.EMPLOYEES ADD CONSTRAINT EMP_DEPT_FK FOREIGN KEY(DEPARTMENT_ID) 
REFERENCES HR.DEPARTMENTS(DEPARTMENT_ID);
-- CONSTRAINT NAME - LOC_C_ID_FK  PARENT TABLE - HR.LOCATIONS  REFERENCE TABLE - HR.COUNTRIES
ALTER TABLE HR.LOCATIONS ADD CONSTRAINT LOC_C_ID_FK FOREIGN KEY(COUNTRY_ID) 
REFERENCES HR.COUNTRIES(COUNTRY_ID);
-- CONSTRAINT NAME - JHIST_EMP_FK  PARENT TABLE - HR.JOB_HISTORY  REFERENCE TABLE - HR.EMPLOYEES
ALTER TABLE HR.JOB_HISTORY ADD CONSTRAINT JHIST_EMP_FK FOREIGN KEY(EMPLOYEE_ID) 
REFERENCES HR.EMPLOYEES(EMPLOYEE_ID);
-- CONSTRAINT NAME - JHIST_JOB_FK  PARENT TABLE - HR.JOB_HISTORY  REFERENCE TABLE - HR.JOBS
ALTER TABLE HR.JOB_HISTORY ADD CONSTRAINT JHIST_JOB_FK FOREIGN KEY(JOB_ID) 
REFERENCES HR.JOBS(JOB_ID);
-- CONSTRAINT NAME - COUNTR_REG_FK  PARENT TABLE - HR.COUNTRIES  REFERENCE TABLE - HR.REGIONS
ALTER TABLE HR.COUNTRIES ADD CONSTRAINT COUNTR_REG_FK FOREIGN KEY(REGION_ID) 
REFERENCES HR.REGIONS(REGION_ID);
-- CONSTRAINT NAME - JHIST_DEPT_FK  PARENT TABLE - HR.JOB_HISTORY  REFERENCE TABLE - HR.DEPARTMENTS
ALTER TABLE HR.JOB_HISTORY ADD CONSTRAINT JHIST_DEPT_FK FOREIGN KEY(DEPARTMENT_ID) 
REFERENCES HR.DEPARTMENTS(DEPARTMENT_ID);
-- CONSTRAINT NAME - EMP_MANAGER_FK  PARENT TABLE - HR.EMPLOYEES  REFERENCE TABLE - HR.EMPLOYEES
ALTER TABLE HR.EMPLOYEES ADD CONSTRAINT EMP_MANAGER_FK FOREIGN KEY(MANAGER_ID) 
REFERENCES HR.EMPLOYEES(EMPLOYEE_ID);

You must run this script after the initial load.

The SMSGC user you create for the target database must have the privileges necessary to insert, update, and delete rows in all these tables.