Map SQL object names for batch translation

This product is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of theService Specific Terms. Pre-GA products are available "as is" and might have limited support. For more information, see thelaunch stage descriptions.

This document describes how to configurename mapping to rename SQLobjects duringbatch translation.

Overview

Name mapping lets you identify the names of SQL objects in yoursource files, and specify target names for those objects inBigQuery. You can use some or all of the following componentsto configure name mapping for an object:

• A name mapping rule, composed of:
  • Sourcename parts that provide the fully qualified name ofthe object in the source system.
  • Atype that identifies the source object's type.
  • Target name parts that provide the name ofthe object in BigQuery.
• Adefault database name to use with any source objectsthat don't specify one.
• Adefault schema name to use with any source objectsthat don't specify one.

Name parts

You provide the values for the source and target object names in a name mappingrule by using a combination of the following name parts:

• Database: The top level of the naming hierarchy. Your source platformmight use an alternative term for this, for exampleproject.
• Schema: The second level of the naming hierarchy. Your source platformmight use an alternative term for this, for exampledataset.
• Relation: The third level of the naming hierarchy. Your source platformmight use an alternative term for this, for exampletable.
• Attribute: The lowest level of the naming hierarchy. Your source platformmight use an alternative term for this, for examplecolumn.

Object types

You must also specify the type of source object you are renaming in aname mapping rule. The following object types are supported:

• Database: A top-level object in the object hierarchy, for exampledatabase.schema.relation.attribute. Your source platformmight use an alternative term for this, for exampleproject. Specifyingdatabase as the object type changes all references to the source string inboth DDL and DML statements.
• Schema: A second-level object in the object hierarchy. Your sourceplatform might use an alternative term for this, for exampledataset.Specifyingschema as the object type changes all references to the sourcestring in both DDL and DML statements.
• Relation: A third-level object in the object hierarchy. Your sourceplatform might use an alternative term for this, for exampletable.Specifyingrelation as the object type changes all references to thesource string in DDL statements.
• Relation alias: An alias for a third-level object. For example, in thequerySELECT t.field1, t.field2 FROM myTable t;,t is a relation alias.In the querySELECT field1, field2 FROM schema1.table1,table1 is alsoa relation alias.Specifyingrelation alias as the object type creates aliases for allreferences to the source string in DML statements. For example, iftableA is specified as the target name, the preceding examples aretranslated asSELECT tableA.field1, tableA.field2 FROM myTable AS tableA;andSELECT tableA.field1, tableA.field2 FROM schema1.table1 AS tableA,respectively.
• Function: A procedure, for examplecreate procedure db.test.function1(a int). Specifyingfunction as theobject type changes all references to the source string in both DDLand DML statements.
• Attribute: A fourth-level object in the object hierarchy. Your sourceplatform might use an alternative term for this, for examplecolumn.Specifyingattribute as the object type changes all references to thesource string in DDL statements.
• Attribute alias: An alias for a fourth-level object. For example, in thequerySELECT field1 FROM myTable;,field1 is an attributealias. Specifyingattribute alias as the object type changes all referencesto the source string in DML statements.

Required name parts for object types

To describe an object in a name mapping rule, use the name parts identified foreach object type in the following table:

Default database

If you want to append a BigQuery project name to all translatedobjects, the easiest thing to do is to specify a default database name whenyoucreate a translation job.This works for source files wherethree-part naming is used, or where four-part naming is used but the highestlevel object name isn't specified.

For example, if you specify the default database namemyproject, then asource statement likeSELECT * FROM database.table is translated toSELECT * FROM myproject.database.table. If you have objects that already use adatabase name part, likeSELECT * FROM database.schema.table, then you haveto use a name mapping rule to renamedatabase.schema.table tomyproject.schema.table.

Default schema

If you want to fully qualify all object names in the source files that don'tuse four-part naming, you can provide both adefault database name and a default schema name whenyoucreate a translation job.The default schema name is provided as the first schema name in the schemasearch path option.

For example, if you specify the default database namemyproject and thedefault schema namemyschema, then the following source statements:

• SELECT * FROM database.table
• SELECT * FROM table1

Are translated to:

• SELECT * FROM myproject.database.table.
• SELECT * FROM myproject.myschema.table1

Name mapping rule behavior

The following sections describe how name mapping rules behave.

Rule inheritance flows down the object hierarchy

A name change that affects a higher-level object affects the target object, andalso all of its child objects in the same hierarchy.

For example, if you specify the following name mapping rule with an objecttype ofschema:

When it is applied, the database and schema name parts of allrelation andattribute objects under thesales_db.cust_mgmt schema are also changed. Forinstance, arelation object namedsales_db.cust_mgmt.history becomessales.cms.history.

Conversely, name changes that target lower-level objects don't affecthigher- or same-level objects in the object hierarchy.

For example, if you specify the following name mapping rule with an objecttype ofrelation:

When it is applied, no other objects at thesales_db orsales_db.cust_mgmtlevel of the object hierarchy have their name changed.

The most specific rule is applied

Only one name mapping rule is applied to an object. If multiple rules couldaffect a single object, the rule that affects the lowest level name partis applied. For example, if adatabase type name mapping rule and aschematype name mapping rule could both affect the name of arelation object, theschema type name mapping rule is applied.

Use a unique combination of type and source values

You can't specify more than one name mapping rule with the same type and sourcevalues. For example, you can't specify both of the following name mapping rules:

Create matchingattribute andattribute alias name mapping rules

When you use anattribute type name mapping rule to change an attribute namein DDL statements, you must create anattribute alias name mapping rule tochange that attribute's name in DML statements as well.

Name changes don't cascade

Name changes don't cascade across name rules.For example, if you created a name mapping rule that renamesdatabase1 toproject1, and another that renamesproject1 toproject2, the translatordoesn't mapdatabase1 toproject2.

Handle source objects that don't have four-part names

Some source systems, like Teradata, use three name parts to fully qualify objectnames. Many source systems also allow you to use partially qualified names intheir SQL dialects,for example usingdatabase1.schema1.table1,schema1.table1, andtable1to refer to the same object in different contexts. If your source files containobjects that don't use four-part object names, you can use name mapping incombination with specifying adefault database nameand adefault schema name to achievethe name mapping that you want.

For examples of using name mapping rules with a default database name or adefault schema name, seeChange the database name part for objects with varying levels of name completionandChange a partially qualified relation object name.

Name mapping examples

Use the examples in this section to see how name mapping rules work for commonuse cases.

Change the database name part for fully qualified objects

The following example renames the database name part fromtd_projecttobq_project for alldatabase,schema,relation, andfunctionobjects that have fully qualified names.

Source and target name parts

Type

• database

Example input

• SELECT * FROM td_project.schema.table;
• SELECT * FROM td_project.schema1.table1;

Example output

• SELECT * FROM bq_project.schema.table;
• SELECT * FROM bq_project.schema1.table1

Change the database name part for objects with varying levels of name completion

The following example renames database name partproject tobq_projectfor all object types, and also addsbq_project as the database name partfor objects that don't specify one.

To do this, you must specify a default database value when configuring thetranslation job, in addition to specifying name mapping rules. For moreinformation on specifying a default database name, seeSubmit a translation job.

Default database value

• project

Source and target name parts

Type

• database

Example input

• SELECT * FROM project.schema.table;
• SELECT * FROM schema1.table1;

Example output

• SELECT * FROM bq_project.schema.table;
• SELECT * FROM bq_project.schema1.table1

Change the database name part and the schema name part for fully qualified objects

The following example changes the database name partwarehouse1 tomyproject, and also changes thedatabase1 schema name parttomydataset.

You can also change the parts of arelation object name in thesame manner, by using arelation type and specifying source and targetvalues for the relation name part.

Source and target name parts

Type

• schema

Example input

• SELECT * FROM warehouse1.database1.table1;
• SELECT * FROM database2.table2;

Example output

• SELECT * FROM myproject.mydataset.table1;
• SELECT * FROM __DEFAULT_DATABASE__.database2.table2;

Change a fully qualifiedrelation object name

The following example renamesmydb.myschema.mytable tomydb.myschema.table1.

Source and target name parts

Type

• relation

Example input

• CREATE table mydb.myschema.mytable(id int, name varchar(64));

Example output

• CREATE table mydb.myschema.table1(id integer, name string(64));

Change a partially qualifiedrelation object name

The following example renamesmyschema.mytable tomydb.myschema.table1.

Default database value

• mydb

Source and target name parts

Type

• relation

Example input

• CREATE table myschema.mytable(id int, name varchar(64));

Example output

• CREATE table mydb.myschema.table1(id integer, name string(64));

Change arelation alias object name

The following example renames all instances of therelation alias objecttable tot.

Source and target name parts

Type

• relation alias

Example input

• SELECT table.id, table.name FROM mydb.myschema.mytable table

Example output

• SELECT t.id, t.name FROM mydb.myschema.mytable AS t

Change afunction object name

The following example renamesmydb.myschema.myfunction tomydb.myschema.function1.

Source and target name parts

Type

• function

Example input

• CREATE PROCEDURE mydb.myschema.myprocedure(a int) BEGIN declare i int; SET i = a + 1; END;
• CALL mydb.myschema.myprocedure(7)

Example output

• CREATE PROCEDURE mydb.myschema.procedure1(a int) BEGIN declare i int; SET i = a + 1; END;
• CALL mydb.myschema.procedure1(7);

Change anattribute object name

The following example renamesmydb.myschema.mytable.myfield tomydb.myschema.mytable.field1. Becauseattribute objects are at the lowestlevel of the object hierarchy, this name mapping does not change the nameof any other object.

Source and target name parts

Type

• attribute

Example input

• CREATE table mydb.myschema.mytable(myfield int, name varchar(64), revenue int);

Example output

• CREATE table mydb.myschema.mytable(field1 int, name varchar(64), revenue int);

Change anattribute alias object name

The following example renamesmydb.myschema.mytable.myfield tomydb.myschema.mytable.field1. Becauseattribute alias objects are at thelowest level of the object hierarchy, this name mapping does not change thename of any other object.

Source and target name parts

Type

• attribute alias

Example input

• SELECT myfield, name FROM mydb.myschema.mytable;

Example output

• SELECT field1, name FROM mydb.myschema.mytable;

JSON file format

If you choose to specify name mapping rules by using a JSON file rather thanthe Google Cloud console, the JSON file must follow this format:

{"name_map":[{"source":{"type":"string","database":"string","schema":"string","relation":"string","attribute":"string"},"target":{"database":"string","schema":"string","relation":"string","attribute":"string"}}]}

The file size must be less than 5 MB.

For more information on specifying name mapping rules for a translation job, seeSubmit a translation job.

JSON examples

The following examples show how to specify name mapping rules by usingJSON files.

Example 1

The name mapping rules in this example make the following object name changes:

• Rename instances of theproject.dataset2.table2relation object tobq_project.bq_dataset2.bq_table2.
• Renames all instances of theprojectdatabase object tobq_project. Forexample,project.mydataset.table2 becomesbq_project.mydataset.table2,andCREATE DATASET project.mydataset becomesCREATE DATASET bq_project.mydataset.

{"name_map":[{"source":{"type":"RELATION","database":"project","schema":"dataset2","relation":"table2"},"target":{"database":"bq_project","schema":"bq_dataset2","relation":"bq_table2"}},{"source":{"type":"DATABASE","database":"project"},"target":{"database":"bq_project"}}]}

Example 2

The name mapping rules in this example make the following object name changes:

• Rename instances of theproject.dataset2.table2.field1attribute object tobq_project.bq_dataset2.bq_table2.bq_field in both DDL and DML statements.

{"name_map":[{"source":{"type":"ATTRIBUTE","database":"project","schema":"dataset2","relation":"table2","attribute":"field1"},"target":{"database":"bq_project","schema":"bq_dataset2","relation":"bq_table2","attribute":"bq_field"}},{"source":{"type":"ATTRIBUTE_ALIAS","database":"project","schema":"dataset2","relation":"table2","attribute":"field1"},"target":{"database":"bq_project","schema":"bq_dataset2","relation":"bq_table2","attribute":"bq_field"}}]}