Reference:
The Electronic Batch project uses the Data Ocean Domains of Industrial, HR, Structure, & Procurement
In each domain environment, the dataset DS_prj_data_sad_ebatch must be created.
This can be done with the following SQL statement
CREATE SCHEMA IF NOT EXISTS DS_prj_data_sad_ebatch OPTIONS (location="EU")
On the Ebatch Projects (prj-data-sad-ebatch-(prod/ppd/test/dev) the following datasets need to be created: STG, ODS, ODS_DataOcean, DataOcean, DM
SELECT 1 from region-eu.INFORMATION_SCHEMA.TABLES GROUP BY 1;
CREATE SCHEMA IF NOT EXISTS DM OPTIONS (location="EU");
CREATE SCHEMA IF NOT EXISTS STG OPTIONS (location="EU");
CREATE SCHEMA IF NOT EXISTS ODS OPTIONS (location="EU");
CREATE SCHEMA IF NOT EXISTS ODS_DataOcean OPTIONS (location="EU");
CREATE SCHEMA IF NOT EXISTS DataOcean OPTIONS (location="EU");
All actions concerning permissions and dataset authorization are manual actions that only need to be performed one time.
The dataset DM on the Ebatch project must be added as an authorized view to the dataset STG, ODS, ODS_DataOcean, & DataOcean
These datasets must be shared with the webapp service account sa-bqsync@prj-ebatch-{env}.iam.gserviceaccount.com with the permission BigQuery Data Viewer.
On the project level, the same service account sa-bqsync@prj-ebatch-{env}.iam.gserviceaccount.com must have BigQuery Job User permission.
On the Industrial and HR domains, this dataset: DS_prj_data_sad_ebatch must be added as an authorized dataset on the datasets ODS & STG.
On the Procurement and Structure domains, this dataset must be added as an authorized dataset on the dataset DM.
Additionally, on the Industrial and HR domains, the dataset DS_prj_data_sad_ebatch must add the dataset ODS_DataOcean on ebatch project as an authorized dataset.
On
Pay special note that the environments (prod/ppd/test/dev) match between the domains and ebatch project.
When promoting changing between environments, the most simple procedure is to take the DDL statements from the lower level environment, and create new DDL statements for the higher level environment
To do this, use the example query to generate DDL statements from the DEV environment for TEST environment for the domains.
If moving from TEST to PPD, simply modify the items in bold.
Running this query will produce the necessary queries to run on each project to update the views.
WITH
view_ddl AS (
SELECT
table_catalog,
ddl
FROM
`prj-data-dm-industrial-dev.DS_prj_data_sad_ebatch.INFORMATION_SCHEMA.TABLES`
WHERE
table_type = 'VIEW'
UNION ALL
SELECT
table_catalog,
ddl
FROM
`prj-data-dm-hr-dev.DS_prj_data_sad_ebatch.INFORMATION_SCHEMA.TABLES`
WHERE
table_type = 'VIEW'
UNION ALL
SELECT
table_catalog,
ddl
FROM
`prj-data-dm-structure-dev.DS_prj_data_sad_ebatch.INFORMATION_SCHEMA.TABLES`
WHERE
table_type = 'VIEW'
UNION ALL
SELECT
table_catalog,
ddl
FROM
`prj-data-dm-procurement-dev.DS_prj_data_sad_ebatch.INFORMATION_SCHEMA.TABLES`
WHERE
table_type = 'VIEW' )
SELECT
table_catalog AS project_id,
STRING_AGG( CONCAT( "CREATE OR REPLACE VIEW `", REPLACE( REGEXP_EXTRACT(ddl, r'`([^`]+)`'), "dev", "test" ), "`", SUBSTR(ddl, INSTR(ddl, "`", INSTR(ddl, "`") + 1) + 1) ), '\n\n' ) AS modified_ddl
FROM
view_ddl
GROUP BY
project_id;
The same process can be applied to the Ebatch project by running this query.
This example generates the DDL statements from DEV for TEST
Think to update the environments listed in Bold if changing between other environments.
WITH view_ddl AS (
SELECT
table_name,
ddl
FROM
`prj-data-sad-ebatch-dev.DataOcean.INFORMATION_SCHEMA.TABLES`
WHERE
table_type = 'VIEW'
)
SELECT
STRING_AGG(
CONCAT(
"CREATE OR REPLACE VIEW `",
REPLACE(
REGEXP_EXTRACT(ddl, r'`([^`]+)`'),
"-dev",
"-test"
),
"`",
SUBSTR(
REPLACE(
SUBSTR(ddl, INSTR(ddl, "`", INSTR(ddl, "`") + 1) + 1),
"-dev",
"-test"
),
1
)
),
'\n\n'
) AS modified_ddl
FROM
view_ddl;