Migration assessment

Teradata

Requirements

• A machine connected to your source Teradata data warehouse (Teradata 15 and later are supported)
• A Google Cloud account with a Cloud Storage bucket to store the data
• An empty BigQuery dataset to store the results
• Read permissions on the dataset to view the results
• Recommended: Administrator-level access rights to the source database when using the extraction tool to access system tables

Requirement: Enable logging

The dwh-migration-dumper tool extracts three types of logs: query logs, utility logs, and resource usage logs. You need to enable logging for the following types of logs to view more thorough insights:

• Query logs: Extracted from the view dbc.QryLogV and from the table dbc.DBQLSqlTbl. Enable logging by specifying the WITH SQL option.
• Utility logs: Extracted from the table dbc.DBQLUtilityTbl. Enable logging by specifying the WITH UTILITYINFO option.
• Resource usage logs: Extracted from the tables dbc.ResUsageScpu and dbc.ResUsageSpma. Enable RSS logging for these two tables.

Run the dwh-migration-dumper tool

Download the dwh-migration-dumper tool.

Download the SHA256SUMS.txt file and run the following command to verify zip correctness:

sha256sum --check SHA256SUMS.txt

(Get-FileHash RELEASE_ZIP_FILENAME).Hash -eq ((Get-Content SHA256SUMS.txt) -Split " ")[0]

For details about how to set up and use the extraction tool, see Generate metadata for translation and assessment.

Use the extraction tool to extract logs and metadata from your Teradata data warehouse as two zip files. Run the following commands on a machine with access to the source data warehouse to generate the files.

Generate the metadata zip file:

dwh-migration-dumper \
  --connector teradata \
  --database DATABASES \
  --driver path/terajdbc4.jar \
  --host HOST \
  --assessment \
  --user USER \
  --password PASSWORD

Generate the zip file containing query logs:

dwh-migration-dumper \
  --connector teradata-logs \
  --driver path/terajdbc4.jar \
  --host HOST \
  --assessment \
  --user USER \
  --password PASSWORD

Replace the following:

• DATABASES: the comma-separated list of database names to extract
• PATH: the absolute or relative path to the driver JAR file to use for this connection
• VERSION: the version of your driver
• HOST: the host address
• USER: the username to use for the database connection
• PASSWORD: the password to use for the database connection

  If left empty, the user is prompted for their password.

You can only use --database flag for the teradata connector. This flag lets you extract the metadata of one or more databases. When you extract the query logs by using the teradata-logs connector, the --database flag is not available. Query logs are always extracted for all the databases.

By default, the query logs are extracted from the view dbc.QryLogV and from the table dbc.DBQLSqlTbl. If you need to extract the query logs from an alternative location, you can specify the names of the tables or views by using the -Dteradata-logs.query-logs-table and -Dteradata-logs.sql-logs-table flags.

By default, the utility logs are extracted from the table dbc.DBQLUtilityTbl. If you need to extract the utility logs from an alternative location, you can specify the name of the table using the -Dteradata-logs.utility-logs-table flag.

By default, the resource usage logs are extracted from the tables dbc.ResUsageScpu and dbc.ResUsageSpma. If you need to extract the resource usage logs from an alternative location, you can specify the names of the tables using the -Dteradata-logs.res-usage-scpu-table and -Dteradata-logs.res-usage-spma-table flags.

For example:

dwh-migration-dumper \
  --connector teradata-logs \
  --driver path/terajdbc4.jar \
  --host HOST \
  --assessment \
  --user USER \
  --password PASSWORD \
  -Dteradata-logs.query-logs-table=pdcrdata.QryLogV_hst \
  -Dteradata-logs.sql-logs-table=pdcrdata.DBQLSqlTbl_hst \
  -Dteradata-logs.log-date-column=LogDate \
  -Dteradata-logs.utility-logs-table=pdcrdata.DBQLUtilityTbl_hst \
  -Dteradata-logs.res-usage-scpu-table=pdcrdata.ResUsageScpu_hst \
  -Dteradata-logs.res-usage-spma-table=pdcrdata.ResUsageSpma_hst

dwh-migration-dumper `
  --connector teradata-logs `
  --driver path\terajdbc4.jar `
  --host HOST `
  --assessment `
  --user USER `
  --password PASSWORD `
  "-Dteradata-logs.query-logs-table=pdcrdata.QryLogV_hst" `
  "-Dteradata-logs.sql-logs-table=pdcrdata.DBQLSqlTbl_hst" `
  "-Dteradata-logs.log-date-column=LogDate" `
  "-Dteradata-logs.utility-logs-table=pdcrdata.DBQLUtilityTbl_hst" `
  "-Dteradata-logs.res-usage-scpu-table=pdcrdata.ResUsageScpu_hst" `
  "-Dteradata-logs.res-usage-spma-table=pdcrdata.ResUsageSpma_hst"

By default, the dwh-migration-dumper tool extracts the last seven days of query logs. Google recommends that you provide at least two weeks of query logs to be able to view more thorough insights. You can specify a custom time range by using the --query-log-start and --query-log-end flags. For example:

dwh-migration-dumper \
  --connector teradata-logs \
  --driver path/terajdbc4.jar \
  --host HOST \
  --assessment \
  --user USER \
  --password PASSWORD \
  --query-log-start "2023-01-01 00:00:00" \
  --query-log-end "2023-01-15 00:00:00"

You can also generate multiple zip files containing query logs covering different periods and provide all of them for assessment.

Amazon Redshift

Requirements

• A machine connected to your source Amazon Redshift data warehouse
• A Google Cloud account with a Cloud Storage bucket to store the data
• An empty BigQuery dataset to store the results
• Read permissions on the dataset to view the results
• Recommended: Super user access to the database when using the extraction tool to access system tables

Run the dwh-migration-dumper tool

Download the dwh-migration-dumper command-line extraction tool.

Download the SHA256SUMS.txt file and run the following command to verify zip correctness:

sha256sum --check SHA256SUMS.txt

(Get-FileHash RELEASE_ZIP_FILENAME).Hash -eq ((Get-Content SHA256SUMS.txt) -Split " ")[0]

For details about how to use the dwh-migration-dumper tool, see the generate metadata page.

Use the dwh-migration-dumper tool to extract logs and metadata from your Amazon Redshift data warehouse as two zip files. Run the following commands on a machine with access to the source data warehouse to generate the files.

Generate the metadata zip file:

dwh-migration-dumper \
  --connector redshift \
  --database DATABASE \
  --driver PATH/redshift-jdbc42-VERSION.jar \
  --host host.region.redshift.amazonaws.com \
  --assessment \
  --user USER \
  --iam-profile IAM_PROFILE_NAME

Generate the zip file containing query logs:

dwh-migration-dumper \
  --connector redshift-raw-logs \
  --database DATABASE \
  --driver PATH/redshift-jdbc42-VERSION.jar \
  --host host.region.redshift.amazonaws.com \
  --assessment \
  --user USER \
  --iam-profile IAM_PROFILE_NAME

Replace the following:

• DATABASE: the name of the database to connect to
• PATH: the absolute or relative path to the driver JAR file to use for this connection
• VERSION: the version of your driver
• USER: the username to use for the database connection
• IAM_PROFILE_NAME: the Amazon Redshift IAM profile name. Required for Amazon Redshift authentication and for AWS API access. To get the description of Amazon Redshift clusters, use the AWS API.

By default, Amazon Redshift stores three to five days of query logs.

By default, the dwh-migration-dumper tool extracts the last seven days of query logs.

Google recommends that you provide at least two weeks of query logs to be able to view more thorough insights. You might need to run the extraction tool a few times over the course of two weeks to get the best results. You can specify a custom range by using the --query-log-start and --query-log-end flags. For example:

dwh-migration-dumper \
  --connector redshift-raw-logs \
  --database DATABASE \
  --driver PATH/redshift-jdbc42-VERSION.jar \
  --host host.region.redshift.amazonaws.com \
  --assessment \
  --user USER \
  --iam-profile IAM_PROFILE_NAME \
  --query-log-start "2023-01-01 00:00:00" \
  --query-log-end "2023-01-02 00:00:00"

You can also generate multiple zip files containing query logs covering different periods and provide all of them for assessment.

Apache Hive

Requirements

• A machine connected to your source Apache Hive data warehouse (BigQuery migration assessment supports Hive on Tez and MapReduce, and supports Apache Hive versions between 2.2 and 3.1, inclusively)
• A Google Cloud account with a Cloud Storage bucket to store the data
• An empty BigQuery dataset to store the results
• Read permissions on the dataset to view the results
• Access to your source Apache Hive data warehouse to configure query logs extraction
• Up to date tables, partitions, and columns statistics

The BigQuery migration assessment uses tables, partitions, and columns statistics to understand your Apache Hive data warehouse better and provide thorough insights. If the hive.stats.autogather configuration setting is set to false in your source Apache Hive data warehouse, Google recommends enabling it or updating statistics manually before running the dwh-migration-dumper tool.

Run the dwh-migration-dumper tool

Download the dwh-migration-dumper command-line extraction tool.

Download the SHA256SUMS.txt file and run the following command to verify zip correctness:

sha256sum --check SHA256SUMS.txt

(Get-FileHash RELEASE_ZIP_FILENAME).Hash -eq ((Get-Content SHA256SUMS.txt) -Split " ")[0]

For details about how to use the dwh-migration-dumper tool, see Generate metadata for translation and assessment.

Use the dwh-migration-dumper tool to generate metadata from your Hive data warehouse as a zip file.

Without Authentication

To generate the metadata zip file, run the following command on a machine that has access to the source data warehouse:

dwh-migration-dumper \
  --connector hiveql \
  --database DATABASES \
  --host hive.cluster.host \
  --port 9083 \
  --assessment

With Kerberos Authentication

To authenticate to the metastore, sign in as a user that has access to the Hive metastore and generate a Kerberos ticket. Then, generate the metadata zip file with the following command:

JAVA_OPTS="-Djavax.security.auth.useSubjectCredsOnly=false" \
  dwh-migration-dumper \
  --connector hiveql \
  --database DATABASES \
  --host hive.cluster.host \
  --port 9083 \
  --hive-kerberos-url PRINCIPAL/HOST \
  -Dhiveql.rpc.protection=hadoop.rpc.protection \
  --assessment

Replace the following:

• DATABASES: the comma-separated list of database names to extract. If not provided, all databases are extracted.
• PRINCIPAL: the kerberos principal that the ticket is issued to
• HOST: the kerberos hostname that the ticket is issued to
• hadoop.rpc.protection: the Quality of Protection (QOP) of the Simple Authentication and Security Layer (SASL) configuration level, equal to the value of hadoop.rpc.protection parameter inside the /etc/hadoop/conf/core-site.xml file, with one of the following values:
  • authentication
  • integrity
  • privacy

Extract query logs with the hadoop-migration-assessment logging hook

To extract query logs, follow these steps:

1. Upload the hadoop-migration-assessment logging hook.
2. Configure the logging hook properties.
3. Verify the logging hook.

Upload the hadoop-migration-assessment logging hook

1. Download the hadoop-migration-assessment query logs extraction logging hook that contains the Hive logging hook JAR file.

2. Extract the JAR file.

   If you need to audit the tool to ensure that it meets compliance requirements, review the source code from the hadoop-migration-assessment logging hook GitHub repository, and compile your own binary.

3. Copy the JAR file into the auxiliary library folder on all clusters where you plan to enable the query logging. Depending on your vendor, you need to locate the auxiliary library folder in cluster settings and transfer the JAR file to the auxiliary library folder on the Hive cluster.

4. Set up configuration properties for hadoop-migration-assessment logging hook. Depending on your Hadoop vendor, you need to use the UI console to edit cluster settings. Modify the /etc/hive/conf/hive-site.xml file or apply the configuration with the configuration manager.

Configure properties

If you already have other values for the following configuration keys, append the settings using a comma (,). To set up hadoop-migration-assessment logging hook, the following configuration settings are required:

• hive.exec.failure.hooks: com.google.cloud.bigquery.dwhassessment.hooks.MigrationAssessmentLoggingHook
• hive.exec.post.hooks : com.google.cloud.bigquery.dwhassessment.hooks.MigrationAssessmentLoggingHook
• hive.exec.pre.hooks: com.google.cloud.bigquery.dwhassessment.hooks.MigrationAssessmentLoggingHook
• hive.aux.jars.path: include the path to the logging hook JAR file, for example file:///HiveMigrationAssessmentQueryLogsHooks_deploy.jar.
• dwhassessment.hook.base-directory: path to the query logs output folder. For example, hdfs://tmp/logs/.

• You can also set the following optional configurations:

  • dwhassessment.hook.queue.capacity: the queue capacity for the query events logging threads. The default value is 64.
  • dwhassessment.hook.rollover-interval: the frequency at which the file rollover must be performed. For example, 600s. The default value is 3600 seconds (1 hour).
  • dwhassessment.hook.rollover-eligibility-check-interval: the frequency at which the file rollover eligibility check is triggered in the background. For example, 600s. The default value is 600 seconds (10 minutes).

Verify the logging hook

After you restart the hive-server2 process, run a test query and analyze your debug logs. You can see the following message:

Logger successfully started, waiting for query events. Log directory is '[dwhassessment.hook.base-directory value]'; rollover interval is '60' minutes;
rollover eligibility check is '10' minutes

The logging hook creates a date-partitioned subfolder in the configured folder. The Avro file with query events appears in that folder after the dwhassessment.hook.rollover-interval interval or hive-server2 process termination. You can look for similar messages in your debug logs to see the status of the rollover operation:

Updated rollover time for logger ID 'my_logger_id' to '2023-12-25T10:15:30'

Performed rollover check for logger ID 'my_logger_id'. Expected rollover time
is '2023-12-25T10:15:30'

Rollover happens at the specified intervals or when the day changes. When the date changes, the logging hook also creates a new subfolder for that date.

Google recommends that you provide at least two weeks of query logs to be able to view more thorough insights.

You can also generate folders containing query logs from different Hive clusters and provide all of them for a single assessment.

Snowflake

Requirements

You must meet the following requirements in order to extract metadata and query logs from Snowflake:

• A machine that can connect to your Snowflake instance(s).
• A Google Cloud account with a Cloud Storage bucket to store the data.
• An empty BigQuery dataset to store the results. Alternatively, you can create a BigQuery dataset when you create the assessment job using the Google Cloud console UI.
• Access to the ACCOUNTADMIN role to your Snowflake instance, or be granted a role with the IMPORTED PRIVILEGES privileges on the database Snowflake by an account admin.

Run the dwh-migration-dumper tool

Download the dwh-migration-dumper command-line extraction tool.

Download the SHA256SUMS.txt file and run the following command to verify zip correctness:

sha256sum --check SHA256SUMS.txt

(Get-FileHash RELEASE_ZIP_FILENAME).Hash -eq ((Get-Content SHA256SUMS.txt) -Split " ")[0]

For details about how to use the dwh-migration-dumper tool, see the generate metadata page.

Use the dwh-migration-dumper tool to extract logs and metadata from your Snowflake data warehouse as two zip files. Run the following commands on a machine with access to the source data warehouse to generate the files.

Generate the metadata zip file:

dwh-migration-dumper \
  --connector snowflake \
  --host HOST_NAME \
  --database SNOWFLAKE \
  --user USER_NAME \
  --role ROLE_NAME \
  --warehouse WAREHOUSE \
  --assessment \
  --password PASSWORD

Generate the zip file containing query logs:

dwh-migration-dumper \
  --connector snowflake-logs \
  --host HOST_NAME \
  --database SNOWFLAKE \
  --user USER_NAME \
  --role ROLE_NAME \
  --warehouse WAREHOUSE \
  --query-log-start STARTING_DATE \
  --query-log-end ENDING_DATE \
  --assessment \
  --password PASSWORD

Replace the following:

• HOST_NAME: the host name of your Snowflake instance.
• USER_NAME: the username to use for the database connection, where the user must have the access permissions as detailed in the requirements section.
• ROLE_NAME: (Optional) the user role when running the dwh-migration-dumper tool—for example, ACCOUNTADMIN.
• WAREHOUSE: the warehouse used to execute the dumping operations. If you have multiple virtual warehouses, you can specify any warehouse to execute this query. Running this query with the access permissions detailed in the requirements section extracts all warehouse artefacts in this account.
• STARTING_DATE: (Optional) used to indicate the start date in a date range of query logs, written in the format YYYY-MM-DD.
• ENDING_DATE: (Optional) used to indicate the end date in a date range of query logs, written in the format YYYY-MM-DD.

You can also generate multiple zip files containing query logs covering non-overlapping periods and provide all of them for assessment.

Oracle

To request feedback or support for this feature, send an email to [email protected].

Requirements

You must meet the following requirements in order to extract metadata and query logs from Oracle:

• A machine that can connect to your Oracle instance(s).
• Java 8 or higher.
• A Google Cloud account with a Cloud Storage bucket to store the data.
• An empty BigQuery dataset to store the results. Alternatively, you can create a BigQuery dataset when you create the assessment job using the Google Cloud console UI.
• An Oracle common user with SYSDBA privileges.

Run the dwh-migration-dumper tool

Download the dwh-migration-dumper command-line extraction tool.

Download the SHA256SUMS.txt file and run the following command to verify zip correctness:

sha256sum --check SHA256SUMS.txt

For details about how to use the dwh-migration-dumper tool, see the generate metadata page.

Use the dwh-migration-dumper tool to extract metadata and performance statistics to the zip file. By default, statistics are extracted from the Oracle AWR that requires the Oracle Tuning and Diagnostics Pack. If this data is not available, dwh-migration-dumper uses STATSPACK instead.

For multitenant databases, the dwh-migration-dumper tool must be executed in the root container. Running it in one of the pluggable databases results in missing performance statistics and metadata about other pluggable databases.

Generate the metadata zip file:

dwh-migration-dumper \
  --connector oracle-stats \
  --host HOST_NAME \
  --port PORT \
  --oracle-service SERVICE_NAME \
  --assessment \
  --driver JDBC_DRIVER_PATH \
  --user USER_NAME \
  --password

Replace the following:

• HOST_NAME: the host name of your Oracle instance.
• PORT: the connection port number. The default value is 1521.
• SERVICE_NAME: the Oracle service name to use for the connection.
• JDBC_DRIVER_PATH: the absolute or relative path to the driver JAR file. You can download this file from the Oracle JDBC driver downloads page. You should select the driver version that is compatible with your database version.
• USER_NAME: name of the user used to connect to your Oracle instance. The user must have the access permissions as detailed in the requirements section.

Teradata

Upload the metadata and one or more zip files containing query logs to your Cloud Storage bucket. For more information about creating buckets and uploading files to Cloud Storage, see Create buckets and Upload objects from a filesystem. The limit for the total uncompressed size of all the files inside the metadata zip file is 50 GB.

The entries in all the zip files containing query logs are divided into the following:

• Query history files with the query_history_ prefix.
• Time series files with the utility_logs_, dbc.ResUsageScpu_, and dbc.ResUsageSpma_ prefixes.

The limit for the total uncompressed size of all the query history files is 5 TB. The limit for the total uncompressed size of all the time series files is 1 TB.

In case the query logs are archived in a different database, see the description of the -Dteradata-logs.query-logs-table and -Dteradata-logs.sql-logs-table flags earlier in this section, which explains how to provide an alternative location for the query logs.

Amazon Redshift

Upload the metadata and one or more zip files containing query logs to your Cloud Storage bucket. For more information about creating buckets and uploading files to Cloud Storage, see Create buckets and Upload objects from a filesystem. The limit for the total uncompressed size of all the files inside the metadata zip file is 50 GB.

The entries in all the zip files containing query logs are divided into the following:

• Query history files with the querytext_ and ddltext_ prefixes.
• Time series files with the query_queue_info_, wlm_query_, and querymetrics_ prefixes.

The limit for the total uncompressed size of all the query history files is 5 TB. The limit for the total uncompressed size of all the time series files is 1 TB.

Apache Hive

Upload the metadata and folders containing query logs from one or multiple Hive clusters to your Cloud Storage bucket. For more information about creating buckets and uploading files to Cloud Storage, see Create buckets and Upload objects from a filesystem.

The limit for the total uncompressed size of all the files inside the metadata zip file is 50 GB.

You can use Cloud Storage connector to copy query logs directly to the Cloud Storage folder. The folders containing subfolders with query logs must be uploaded to the same Cloud Storage folder, where the metadata zip file is uploaded.

Query logs folders have query history files with the dwhassessment_ prefix. The limit for the total uncompressed size of all the query history files is 5 TB.

Snowflake

Upload the metadata and the zip file(s) containing query logs and usage histories to your Cloud Storage bucket. When uploading these files to Cloud Storage, the following requirements must be met:

• The total uncompressed size of all the files inside the metadata zip file must be less than 50 GB.
• The metadata zip file and the zip file containing query logs must be uploaded to a Cloud Storage folder. If you have multiple zip files containing non-overlapping query logs, you can upload all of them.
• You must upload all the files to the same Cloud Storage folder.
• You must upload all of the metadata and query logs zip files exactly as they are output by dwh-migration-dumper tool. Don't unzip, combine, or otherwise modify them.
• The total uncompressed size of all the query history files must be less than 5 TB.

For more information about creating buckets and uploading files to Cloud Storage, see Create buckets and Upload objects from a filesystem.

Oracle

To request feedback or support for this feature, send email to [email protected].

Upload the zip file containing metadata and performance statistics to a Cloud Storage bucket. By default, the filename for the zip file is dwh-migration-oracle-stats.zip, but you can customize this by specifying it in the --output flag. The limit for the total uncompressed size of all the files inside the zip file is 50 GB.

For more information about creating buckets and uploading files to Cloud Storage, see Create buckets and Upload objects from a filesystem.

Console

1. In the Google Cloud console, go to the BigQuery page.

   Go to BigQuery

2. In the navigation panel, go to Assessment.

3. Click Start Assessment.

4. Fill in the assessment configuration dialog.

   1. For Display name, enter the name which can contain letters, numbers or underscores. This name is only for display purposes and does not have to be unique.

   2. In the Data location list, choose a location for the assessment job. The assessment job must be located in the same location as your extracted files input Cloud Storage bucket and your output BigQuery dataset.

      However, if this location is a US or EU multi-region, then the Cloud Storage bucket location and the BigQuery dataset location can be in any of the regions inside this multi-region. The Cloud Storage bucket and the BigQuery dataset can be located in different locations within the same multi-region. For example, if you select the US multi-region, then the Cloud Storage bucket can be in the us-central1 region while the BigQuery dataset can be in the us-east1 region.

   3. For Assessment data source, choose your data warehouse.

   4. For Path to input files, enter the path to the Cloud Storage bucket that contains your extracted files.

   5. To choose how your assessment results are stored, do one of the following options:

      • Keep the Automatically create the new BigQuery dataset checkbox selected to have the BigQuery dataset created automatically. The name of the dataset is generated automatically.
      • Clear the Automatically create the new BigQuery dataset checkbox and either choose the existing empty BigQuery dataset using the format projectId.datasetId, or create a new dataset name. In this option you can choose the BigQuery dataset name.

   Option 1 - automatic BigQuery dataset generation (default)

   Option 2 - manual BigQuery dataset creation:

5. Click Create. You can see the status of the job in the assessment jobs list.

   While the assessment is running, you can check its progress and estimated time to complete in the tooltip of the status icon.

   

6. While the assessment is running, you can click the View report link in the assessment jobs list to view the assessment report with partial data in Looker Studio. The View report link might take some time to appear while the assessment is running. The report opens in a new tab.

   The report is updated with new data as they are processed. Refresh the tab with the report or click View report again to see the updated report.

7. After the assessment is complete, click View report to view the complete assessment report in Looker Studio. The report opens in a new tab.

API

Call the create method with a defined workflow.

Then call the start method to start the assessment workflow.

Teradata

The report is a three-part narrative that's prefaced by a summary highlights page. That page includes the following sections:

• Existing system. This section is a snapshot of the existing Teradata system and usage, including the number of databases, schemas, tables, and total size in TB. It also lists the schemas by size and points to potential sub-optimal resource utilization (tables with no writes or few reads).
• BigQuery steady state transformations (suggestions). This section shows what the system will look like on BigQuery after migration. It includes suggestions for optimizing workloads on BigQuery (and avoiding wastage).
• Migration plan. This section provides information about the migration effort itself—for example, getting from the existing system to the BigQuery steady state. This section includes the count of queries that were automatically translated and the expected time to move each table into BigQuery.

The details of each section include the following:

Existing system

• Compute & Queries
  • CPU utilization:
    • Heatmap of hourly average CPU utilization (overall system resource utilization view)
    • Queries by hour and day with CPU utilization
    • Queries by type (read/write) with CPU utilization
    • Applications with CPU utilization
    • Overlay of the hourly CPU utilization with average hourly query performance and average hourly application performance
  • Queries histogram by type and query durations
  • Applications details view (app, user, unique queries, reporting versus ETL breakdown)
• Storage Overview
  • Databases by volume, views, and access rates
  • Tables with access rates by users, queries, writes, and temporary table creations
• Applications: Access rates and IP addresses

BigQuery steady state transformations (suggestions)

• Join indexes converted to materialized views
• Clustering and partitioning candidates based on metadata and usage
• Low latency queries identified as candidates for BigQuery BI Engine
• Columns configured with default values that use the column description feature to store default values
• Unique indexes in Teradata (to prevent rows with non-unique keys in a table) use staging tables and a MERGE statement to insert only unique records into the target tables and then discard duplicates
• Remaining queries and schema translated as-is

Migration plan

• Detailed view with automatically translated queries
  • Count of total queries with ability to filter by user, application, affected tables, queried tables, and query type
  • Buckets of queries with similar patterns grouped and shown together so that the user is able to see the translation philosophy by query types
• Queries requiring human intervention
  • Queries with BigQuery lexical structure violations
  • User-defined functions and procedures
  • BigQuery reserved keywords
• Tables schedules by writes and reads (to group them for moving)
• Data migration with the BigQuery Data Transfer Service: Estimated time to migrate by table

The Existing System section contains the following views:

System Overview

The System Overview view provides the high-level volume metrics of the key components in the existing system for a specified time period. The timeline that is evaluated depends on the logs that were analyzed by the BigQuery migration assessment. This view gives you quick insight into the source data warehouse utilization, which you can use for migration planning.

Table Volume

The Table Volume view provides statistics on the largest tables and databases found by the BigQuery migration assessment. Because large tables may take longer to extract from the source data warehouse system, this view can be helpful in migration planning and sequencing.

Table Usage

The Table Usage view provides statistics on which tables are heavily used within the source data warehouse system. Heavily used tables can help you to understand which tables might have many dependencies and require additional planning during the migration process.

Applications

The Applications Usage view and the Applications Patterns view provide statistics on applications found during processing of logs. These views let users understand usage of specific applications over time and the impact on resource usage. During a migration, it's important to visualize the ingestion and consumption of data to gain a better understanding of the dependencies of the data warehouse, and to analyze the impact of moving various dependent applications together. The IP Address table can be useful for pinpointing the exact application using the data warehouse over JDBC connections.

Queries

The Queries view gives a breakdown of the types of SQL statements executed and statistics of their usage. You can use the histogram of Query Type and Time to identify low periods of system utilization and optimal times of day to transfer data. You can also use this view to identify frequently executed queries and the users invoking those executions.

Databases

The Databases view provides metrics on the size, tables, views, and procedures defined in the source data warehouse system. This view can give insight into the volume of objects that you need to migrate.

Database Coupling

The Database Coupling view provides a high-level view on databases and tables that are accessed together in a single query. This view can show what tables and databases are referenced often and what you can use for migration planning.

The BigQuery steady state section contains the following views:

Tables With No Usage

The Tables With No Usage view displays tables in which the BigQuery migration assessment could not find any usage during the logs period that was analyzed. A lack of usage might indicate that you don't need to transfer that table to BigQuery during migration or that the costs of storing data in BigQuery could be lower. You should validate the list of unused tables because they could have usage outside of the logs period, such as a table that is only used once every three or six months.

Tables With No Writes

The Tables With No Writes view displays tables in which the BigQuery migration assessment could not find any updates during the logs period that was analyzed. A lack of writes can indicate where you might lower your storage costs in BigQuery.

Low-Latency Queries

The Low-Latency Queries view displays a distribution of query runtimes based on the log data analyzed. If the query duration distribution chart displays a large number of queries with < 1 second in runtime, consider enabling BigQuery BI Engine to accelerate BI and other low-latency workloads.

Materialized Views

The Materialized View provides further optimization suggestions to boost performance on BigQuery.

Clustering and Partitioning

The Partitioning and Clustering view displays tables that would benefit from partitioning, clustering, or both.

The Metadata suggestions are achieved by analyzing the source data warehouse schema (like Partitioning and Primary Key in the source table) and finding the closest BigQuery equivalent to achieve similar optimization characteristics.

The Workload suggestions are achieved by analyzing the source query logs. The recommendation is determined by analyzing the workloads, especially WHERE or JOIN clauses in the analyzed query logs.

Clustering Recommendation

The Partitioning view displays tables which might have greater than 10,000 partitions, based on their partitioning constraint definition. These tables tend to be good candidates for BigQuery clustering, which enables fine-grained table partitions.

Unique Constraints

The Unique Constraints view displays both SET tables and unique indices defined within the source data warehouse. In BigQuery, it's recommended to use staging tables and a MERGE statement to insert only unique records into a target table. Use the contents of this view to help determine which tables you might need to adjust ETL for during the migration.

Default Values / Check Constraints

This view shows tables that use check constraints to set default column values. In BigQuery, see Specify default column values.

The Migration path section of the report contains the following views:

SQL Translation

The SQL Translation view lists the count and details of queries that were automatically converted by BigQuery migration assessment and don't need manual intervention. Automated SQL Translation typically achieves high translation rates if metadata is provided. This view is interactive and allows analysis of common queries and how these are translated.

Offline Effort

The Offline Effort view captures the areas that need manual intervention, including specific UDFs and potential lexical structure and syntax violations for tables or columns.

BigQuery Reserved Keywords

The BigQuery Reserved Keywords view displays detected usage of keywords that have special meaning in the GoogleSQL language, and cannot be used as identifiers unless enclosed by backtick (`) characters.

Table Updates Schedule

The Table Updates Schedule view shows when and how frequently tables are updated to help you plan how and when to move them.

Data Migration to BigQuery

The Data Migration to BigQuery view outlines the migration path with the expected time to migrate your data using the BigQuery Data Transfer Service. For more information, see the BigQuery Data Transfer Service for Teradata guide.

The Appendix section contains the following views:

Case Sensitivity

The Case Sensitivity view shows tables in the source data warehouse that are configured to perform case-insensitive comparisons. By default, string comparisons in BigQuery are case-sensitive. For more information, see Collation.

Amazon Redshift

Migration Highlights

The Migration Highlights view provides an executive summary of the three sections of the report:

1. The Existing System panel provides information on the number of databases, schemas, tables, and the total size of the existing Redshift System. It also lists the schemas by size and potential sub-optimal resource utilization. You can use this information to optimize your data by removing, partitioning, or clustering your tables.
2. The BigQuery Steady State panel provides information on what your data will look like post-migration on BigQuery, including the number of queries that can be automatically translated using BigQuery Migration Service. This section also shows the costs of storing your data in BigQuery based on your annual data ingestion rate, along with optimization suggestions for tables, provisioning, and space.
3. The Migration Path panel provides information on the migration effort itself. For each table it shows the expected time to migrate, the number of rows in the table, and its size.

The Existing System section contains the following views:

Queries by Type and Schedule

The Queries by Type and Schedule view categorizes your queries into ETL/Write and Reporting/Aggregation. Seeing your query mix over time helps you understand your existing usage patterns, and identify burstiness and potential over-provisioning that can impact cost and performance.

Query Queuing

The Query Queuing view provides additional details on system load including query volume, mix, and any performance impacts due to queuing, such as insufficient resources.

Queries and WLM Scaling

The Queries and WLM Scaling view identifies concurrency scaling as an added cost and configuration complexity. It shows how your Redshift system routes queries based on the rules you specified, and performance impacts due to queuing, concurrency scaling, and evicted queries.

Queuing and Waiting

The Queuing and Waiting view is a deeper look into queue and wait times for queries over time.

WLM Classes and Performance

The WLM Classes and Performance view provides an optional way to map your rules to BigQuery. However, we recommend you let BigQuery automatically route your queries.

Query & Table volume insights

The Query & Table volume insights view lists queries by size, frequency, and top users. This helps you categorize the sources of load on the system and plan how to migrate your workloads.

Databases and Schemas

The Databases and Schemas view provides metrics on the size, tables, views, and procedures defined in the source data warehouse system. This provides insight into the volume of objects which need to be migrated.

Table Volume

The Table Volume view provides statistics on the largest tables and databases, showing how they are accessed. Because large tables may take longer to extract from the source data warehouse system, this view helps you with migration planning and sequencing.

Table Usage

The Table Usage view provides statistics on which tables are heavily used within the source data warehouse system. Heavily used tables can be leveraged to understand tables which might have many dependencies and warrant additional planning during the migration process.

Importers & Exporters

The Importers & Exporters view provides information on data and users involved in data import (using COPY queries) and data export (using UNLOAD queries). This view helps to identify staging layer and processes related to ingestion and exports.

Cluster Utilization

The Cluster Utilization view provides general information about all available clusters and displays CPU utilization for each cluster. This view can help you understand system capacity reserve.

The BigQuery steady state section contains the following views:

Clustering & Partitioning

The Partitioning and Clustering view displays tables that would benefit from partitioning, clustering, or both.

The Metadata suggestions are achieved by analyzing the source data warehouse schema (like Sort Key and Dist Key in the source table) and finding the closest BigQuery equivalent to achieve similar optimization characteristics.

The Workload suggestions are achieved by analyzing the source query logs. The recommendation is determined by analyzing the workloads, especially WHERE or JOIN clauses in the analyzed query logs.

At the bottom of the page, there is a translated create table statement with all optimizations provided. All translated DDL statements can be also extracted from the dataset. Translated DDL statements are stored in SchemaConversion table in CreateTableDDL column.

The recommendations in the report are provided only for tables larger than 1 GB because small tables won't benefit from clustering and partitioning. However, DDL for all tables (including tables smaller than 1GB) are available in SchemaConversion table.

Tables With No Usage

The Tables With No Usage view displays tables where the BigQuery migration assessment did not identify any usage during the analyzed logs period. A lack of usage might indicate that you don't need to transfer that table to BigQuery during migration or that the costs of storing data in BigQuery could be lower (billed as Long-term storage). We recommend validating the list of unused tables because they could have usage outside of the logs period, such as a table that is only used once every three or six months.

Tables With No Writes

The Tables With No Writes view displays tables where the BigQuery migration assessment did not identify any updates during the analyzed logs period. A lack of writes can indicate where you might lower your storage costs in BigQuery (billed as Long-term storage).

BI Engine and Materialized Views

The BI Engine and Materialized Views provides further optimization suggestions to boost performance on BigQuery.

The Migration path section contains the following views:

SQL Translation

The SQL Translation view lists the count and details of queries that were automatically converted by BigQuery migration assessment and don't need manual intervention. Automated SQL Translation typically achieves high translation rates if metadata is provided.

SQL Translation Offline Effort

The SQL Translation Offline Effort view captures the areas that need manual intervention, including specific UDFs and queries with potential translation ambiguities.

Alter Table Append Support

The Alter Table Append Support view shows details about common Redshift SQL constructs that do not have a direct BigQuery counterpart.

Copy Command Support

The Copy Command Support view shows details about common Redshift SQL constructs that do not have a direct BigQuery counterpart.

SQL Warnings

The SQL Warnings view captures areas that are successfully translated, but require a review.

Lexical Structure & Syntax Violations

The Lexical Structure & Syntax Violations view displays names of columns, tables, functions, and procedures that violate BigQuery syntax.

BigQuery Reserved Keywords

The BigQuery Reserved Keywords view displays detected usage of keywords that have special meaning in the GoogleSQL language, and cannot be used as identifiers unless enclosed by backtick (`) characters.

Schema Coupling

The Schema Coupling view provides a high-level view on databases, schemas, and tables that are accessed together in a single query. This view can show what tables, schemas, and databases are referenced often and what you can use for migration planning.

Table Updates Schedule

The Table Updates Schedule view shows how when and how frequently tables are updated to help you plan how and when to move them.

Table Scale

The Table Scale view lists your tables with the most columns.

Data Migration to BigQuery

The Data Migration to BigQuery view outlines the migration path with the expected time to migrate your data using the BigQuery Migration Service Data Transfer Service. For more information, see the BigQuery Data Transfer Service for Redshift guide.

Assessment execution summary

The Assessment execution summary contains the report completeness, the progress of the on-going assessment, and the status of processed files and errors.

Report completeness represents the percentage of successfully processed data that is recommended to display meaningful insights in the assessment report. If the data for a particular section of the report is missing, this information is listed in the Assessment Modules table under the Report Completeness indicator.

The progress metric indicates the percentage of the data processed so far along with the estimate of the remaining time to process all of the data. After the processing is complete, the progress metric is not displayed.

Apache Hive

The report consisting a three-part narrative is prefaced by a summary highlights page that includes the following sections:

• Existing System - Hive. This section consists of a snapshot of the existing Hive system and usage including the number of databases, tables, their total size in GB, and the number of query logs processed. This section also lists the databases by size and points to potential sub-optimal resource utilization (tables with no writes or few reads) and provisioning. The details of this section includes the following:

  • Compute and queries
    • CPU utilization:
      • Queries by hour and day with CPU utilization
      • Queries by type (read/write)
      • Queues and applications
      • Overlay of the hourly CPU utilization with average hourly query performance and average hourly application performance
    • Queries histogram by type and query durations
    • Queueing and waiting page
    • Queues detailed view (Queue, user, unique queries, reporting vs ETL breakdown, by metrics)
  • Storage overview
    • Databases by volume, views, and access rates
    • Tables with access rates by users, queries, writes, and temporary table creations
  • Queues and applications: Access rates and client IP addresses

• BigQuery Steady State. This section shows what the system will look like on BigQuery after migration. It includes suggestions for optimizing workloads on BigQuery (and avoiding wastage). The details of this section includes the following:

  • Tables identified as candidates for materialized views
  • Clustering and partitioning candidates based on metadata and usage
  • Low latency queries identified as candidates for BigQuery BI Engine
  • Tables without read or write usage
  • Partitioned tables with the data skew

• Migration Plan. This section provides information about the migration effort itself. For example, getting from the existing system to the BigQuery steady state. This section contains identified storage targets for each table, tables identified as significant for migration, and the count of queries that were automatically translated. The details of this section includes the following:

  • Detailed view with automatically translated queries
    • Count of total queries with ability to filter by user, application, affected tables, queried tables, and query type
    • Query buckets with similar patterns grouped together, enabling users to see the translation philosophy by query types
  • Queries requiring human intervention
    • Queries with BigQuery lexical structure violations
    • User-defined functions and procedures
    • BigQuery reserved keywords
  • Query requiring review
  • Tables schedules by writes and reads (to group them for moving)
  • Identified storage target for external and managed tables

The Existing System - Hive section contains the following views:

System Overview

This view provides the high-level volume metrics of the key components in the existing system for a specified time period. The timeline that is evaluated depends on the logs that were analyzed by the BigQuery migration assessment. This view gives you quick insight into the source data warehouse utilization, which you can use for migration planning.

Table Volume

This view provides statistics on the largest tables and databases found by the BigQuery migration assessment. Because large tables may take longer to extract from the source data warehouse system, this view can be helpful in migration planning and sequencing.

Table Usage

This view provides statistics on which tables are heavily used within the source data warehouse system. Heavily used tables can help you to understand which tables might have many dependencies and require additional planning during the migration process.

Queues Utilization

This view provides statistics on YARN queues usage found during processing of logs. These views let users understand usage of specific queues and applications over time and the impact on resource usage. These views also help identify and prioritize workloads for migration. During a migration, it's important to visualize the ingestion and consumption of data to gain a better understanding of the dependencies of the data warehouse, and to analyze the impact of moving various dependent applications together. The IP address table can be useful for pinpointing the exact application using the data warehouse over JDBC connections.

Queues Metrics

This view provides a breakdown of the different metrics on YARN queues found during processing of logs. This view lets users to understand patterns of usage in specific queues and impact on migration. You can also use this view to identify connections between tables accessed in queries and queues where the query was executed.

Queuing and Waiting

This view provides an insight on the query queuing time in the source data warehouse. Queuing times indicate performance degradation due to under provisioning, and additional provisioning requires increased hardware and maintenance costs.

Queries

This view gives a breakdown of the types of SQL statements executed and statistics of their usage. You can use the histogram of Query Type and Time to identify low periods of system utilization and optimal times of day to transfer data. You can also use this view to identify most-used Hive execution engines and frequently executed queries along with the user details.

Databases

This view provides metrics on the size, tables, views, and procedures defined in the source data warehouse system. This view can give insight into the volume of objects that you need to migrate.

Database & Table Coupling

This view provides a high-level view on databases and tables that are accessed together in a single query. This view can show what tables and databases are referenced often and what you can use for migration planning.

The BigQuery Steady State section contains the following views:

Tables With No Usage

The Tables With No Usage view displays tables in which the BigQuery migration assessment could not find any usage during the logs period that was analyzed. A lack of usage might indicate that you don't need to transfer that table to BigQuery during migration or that the costs of storing data in BigQuery could be lower. You must validate the list of unused tables because they could have usage outside of the logs period, such as a table that is only used once every three or six months.

Tables With No Writes

The Tables With No Writes view displays tables in which the BigQuery migration assessment could not find any updates during the logs period that was analyzed. A lack of writes can indicate where you might lower your storage costs in BigQuery.

Clustering and Partitioning Recommendations

This view displays tables that would benefit from partitioning, clustering, or both.

The Metadata suggestions are achieved by analyzing the source data warehouse schema (like Partitioning and Primary Key in the source table) and finding the closest BigQuery equivalent to achieve similar optimization characteristics.

The Workload suggestions are achieved by analyzing the source query logs. The recommendation is determined by analyzing the workloads, especially WHERE or JOIN clauses in the analyzed query logs.

Partitions converted to Clusters

This view displays tables that have more than 10,000 partitions, based on their partitioning constraint definition. These tables tend to be good candidates for BigQuery clustering, which enables fine-grained table partitions.

Skewed partitions

The Skewed Partitions view displays tables that are based on the metadata analysis and have data skew on one or several partitions. These tables are good candidates for schema change, as queries on skewed partitions might not perform well.

BI Engine and Materialized Views

The Low-Latency Queries and Materialized Views view displays a distribution of query runtimes based on the log data analyzed and a further optimization suggestions to boost performance on BigQuery. If the query duration distribution chart displays a large number of queries with runtime less than 1 second, consider enabling BI Engine to accelerate BI and other low-latency workloads.

The Migration Plan section of the report contains the following views:

SQL Translation

The SQL Translation view lists the count and details of queries that were automatically converted by BigQuery migration assessment and don't need manual intervention. Automated SQL Translation typically achieves high translation rates if metadata is provided. This view is interactive and allows analysis of common queries and how these are translated.

SQL Translation Offline Effort

The Offline Effort view captures the areas that need manual intervention, including specific UDFs and potential lexical structure and syntax violations for tables or columns.

SQL Warnings

The SQL Warnings view captures areas that are successfully translated, but require a review.

BigQuery Reserved Keywords

The BigQuery Reserved Keywords view displays detected usage of keywords that have special meaning in the GoogleSQL language. These keywords can't be used as identifiers unless enclosed by backtick (`) characters.

Table Updates Schedule

The Table Updates Schedule view shows when and how frequently tables are updated to help you plan how and when to move them.

BigLake External Tables

The BigLake External Tables view outlines tables that are identified as targets to migration to BigLake instead of BigQuery.

The Appendix section of the report contains the following views:

Detailed SQL Translation Offline Effort Analysis

The Detailed Offline Effort Analysis view provides an additional insight of the SQL areas that need manual intervention.

Detailed SQL Warnings Analysis

The Detailed Warnings Analysis view provides an additional insight of the SQL areas that are successfully translated, but require a review.

Snowflake

The report consists of different sections that can be used either separately or together. The following diagram organizes these sections into three common user goals to help you assess your migration needs:

Migration Highlights views

The Migration Highlights section contains the following views:

Snowflake vs BigQuery Pricing Models

Listing of the pricings with different tiers/editions. Also includes an illustration of how BigQuery autoscaling can help save more cost compared to that of Snowflake.

Total Cost of Ownership

Interactive table, allowing the user to define: BigQuery Edition, commitment, baseline slot commitment, percentage of active storage, and percentage of data loaded or changed. Helps better estimate the cost for custom cases.

Automatic Translation Highlights

Aggregated translation ratio, grouped by either user or database, ordered ascending or descending. Also includes the most common error message for failed auto translation.

Existing System views

The Existing System section contains the following views:

System Overview

The System Overview view provides the high-level volume metrics of the key components in the existing system for a specified time period. The timeline that is evaluated depends on the logs that were analyzed by the BigQuery migration assessment. This view gives you quick insight into the source data warehouse utilization, which you can use for migration planning.

Virtual Warehouses Overview

Shows the Snowflake cost by warehouse, as well as the node-based rescaling over the period.

Table Volume

The Table Volume view provides statistics on the largest tables and databases found by the BigQuery migration assessment. Because large tables may take longer to extract from the source data warehouse system, this view can be helpful in migration planning and sequencing.

Table Usage

The Table Usage view provides statistics on which tables are heavily used within the source data warehouse system. Heavily used tables can help you to understand which tables might have many dependencies and require additional planning during the migration process.

Queries

The Queries view gives a breakdown of the types of SQL statements executed and statistics of their usage. You can use the histogram of Query Type and Time to identify low periods of system utilization and optimal times of day to transfer data. You can also use this view to identify frequently executed queries and the users invoking those executions.

Databases

The Databases view provides metrics on the size, tables, views, and procedures defined in the source data warehouse system. This view provides insight into the volume of objects that you need to migrate.

BigQuery steady state views

The BigQuery steady state section contains the following views:

Tables With No Usage

The Tables With No Usage view displays tables in which the BigQuery migration assessment could not find any usage during the logs period that was analyzed. This can indicate which tables might not need to be transferred to BigQuery during migration or that the costs of storing data in BigQuery could be lower. You must validate the list of unused tables since they could have usage outside of the logs period analyzed, such as a table which is only used once per quarter or half.

Tables With No Writes

The Tables With No Writes view displays tables in which the BigQuery migration assessment could not find any updates during the logs period that was analyzed. This can indicate that the costs of storing data in BigQuery could be lower.

Migration Plan views

The Migration Plan section of the report contains the following views:

SQL Translation

The SQL Translation view lists the count and details of queries that were automatically converted by BigQuery migration assessment and don't need manual intervention. Automated SQL Translation typically achieves high translation rates if metadata is provided. This view is interactive and allows analysis of common queries and how these are translated.

SQL Translation Offline Effort

The Offline Effort view captures the areas that need manual intervention, including specific UDFs and potential lexical structure and syntax violations for tables or columns.

SQL Warnings - To Review

The Warnings To Review view captures the areas that are mostly translated but requires some human inspection.

BigQuery Reserved Keywords

The BigQuery Reserved Keywords view displays detected usage of keywords that have special meaning in the GoogleSQL language, and cannot be used as identifiers unless enclosed by backtick (`) characters.

Database and Table Coupling

The Database Coupling view provides a high-level view on databases and tables that are accessed together in a single query. This view can show what tables and databases are often referenced and what can be used for migration planning.

Table Updates Schedule

The Table Updates Schedule view shows when and how frequently tables are updated to help you plan how and when to move them.

Proof of Concept views

The PoC (proof of concept) section contains the following views:

PoC for demonstrating steady state BigQuery savings

Includes the most frequent queries, the queries reading the most data, the slowest queries, and the tables impacted by these aforementioned queries.

PoC for demonstrating BigQuery migration plan

Showcases how BigQuery translate the most complex queries and the tables they impact.

Oracle

To request feedback or support for this feature, send email to [email protected].

Migration Highlights

The Migration Highlights section contains the following views:

• Existing system: a snapshot of the existing Oracle system and usage, including the number of databases, schemas, tables, and total size in GB. It also provides the workload classification summary for each database to help you decide if BigQuery is the right migration target.
• Compatibility: provides information about the migration effort itself. For each analyzed database it shows the expected time to migrate and the number of database objects that can be migrated automatically with Google provided tools.
• BigQuery steady state: contains information on what your data will look like post-migration on BigQuery, including the costs of storing your data in BigQuery based on your annual data ingestion rate. In addition, it provides the BigQuery compute cost estimation for Oracle Exadata.

Existing System

The Existing System section contains the following views:

• Workloads Characteristic: describes the workload type for each database based on the analyzed performance metrics. Each database is classified as OLAP, Mixed, or OLTP. This information can help you to make a decision on which databases can be migrated to BigQuery.
• Databases and Schemas: provides a breakdown of total storage size in GB for each database, schema, or table. In addition you can use this view to identify materialized views and external tables.
• Database Features and Links: shows the list of Oracle features used in your database, together with the BigQuery equivalent features or services that can be used after the migration. In addition, you can explore the Database Links to better understand connections between the databases.
• Database Connections: provides insight into the database sessions started by the user or application. Analyzing this data can help you identify external applications that may require additional effort during the migration.
• Query Types: provides a breakdown of the types of SQL statements executed and and statistics of their usage. You can use the hourly histogram of Query Executions or Query CPU Time to identify low periods of system utilization and optimal times of day to transfer data.
• PL/SQL Source Code: provides insight into the PL/SQL objects, like functions or procedures, and their size for each database and schema. In addition, the hourly executions histogram can be used to identify peak hours with most PL/SQL executions.

BigQuery Steady State

The Existing System section contains the following views:

• Exadata vs BigQuery pricing: provides the general comparison of Exadata and BigQuery pricing models to help you understand the benefits and potential cost savings after the migration to BigQuery.
• BigQuery Cost Estimator: can be used to estimate the total BigQuery cost based on your Exadata configuration. To get the most accurate estimate, you should provide the number of database servers, their type and utilization. In addition, you can compare BigQuery cost depending on the selected edition and commitment.
• Database Read/Writes: provides insight into the database physical disk operations. Analyzing this data can help you find the best time to perform data migration from Oracle to BigQuery.

Migration Hints

The Migration Hints section contains the following views:

• Database Objects Compatibility: provides the overview of database objects compatibility with BigQuery, including the number of objects that can be automatically migrated with Google provided tools or require manual action. This information is shown for each database, schemma, and database object type.
• Database Objects Migration Effort: shows the estimate of migration effort in hours for each database, schema, or database object type. In addition it shows the percentage of small, medium, and large objects based on the migration effort.
• Database Schema Migration Effort: provides the list of all detected database object types, their number, compatibility with BigQuery and the estimated migration effort in hours.
• Database Schema Migration Effort Detailed: provides more deep dive insight into the database schema migration effort, including the information for each single object.

Proof of Concept views

The Proof of Concept views section contains the following views:

• Proof of concept migration: shows the suggested list of databases with the lowest migration effort that are good candidates for initial migration. In addition, it shows the top queries that can help to demonstrate the time and cost savings, and value of BigQuery via a proof of concept.

Appendix

The Appendix section contains the following views:

• Assessment Execution Summary: provides the assessment execution details including the list of processed files, errors, and report completeness. You can use this page to investigate missing data in the report and better understand the overall report completeness.

Teradata

AllRIChildren

This table provides the referential integrity information of the table children.

AllRIParents

This table provides the referential integrity information of the table parents.

Columns

This table provides information about the columns.

DiskSpace

This table provides information about the diskspace usage for each database.

Functions

This table provides information about the functions.

Indices

This table provides information about the indices.

Queries

This table provides information about the extracted queries.

QueryLogs

This table provides some execution statistics about the extracted queries.

QueryTypeStatistics

This table provides statistics on types of queries.

ResUsageScpu

This table provides information about CPU resource usage.

Roles

This table provides information about roles.

SchemaConversion

This table provides information on schema conversions that are related to clustering and partitioning.

TableInfo

This table provides information about tables.

TableRelations

This table provides information about tables.

TableSizes

This table provides information about sizes of tables.

Users

This table provides information about users.

Amazon Redshift

Columns

The Columns table comes from one of the following tables: SVV_COLUMNS, INFORMATION_SCHEMA.COLUMNS or PG_TABLE_DEF, ordered by priority. The tool attempts to load data from the highest priority table first. If this fails, then it attempts to load data from the next highest priority table. Refer to the Amazon Redshift or PostgreSQL documentation for more details about the schema and usage.

CreateAndDropStatistic

This table provides information on the creation and deletion of tables.

Databases

This table comes from the PG_DATABASE_INFO table from Amazon Redshift directly. The original field names from the PG table are included with the descriptions. Refer to the Amazon Redshift and PostgreSQL documentation for more details about the schema and usage.

ExternalColumns

This table contains information from the SVV_EXTERNAL_COLUMNS table from Amazon Redshift directly. Refer to the Amazon Redshift documentation for more details about the schema and usage.

ExternalDatabases

This table contains information from the SVV_EXTERNAL_DATABASES table from Amazon Redshift directly. Refer to the Amazon Redshift documentation for more details about the schema and usage.

ExternalPartitions

This table contains information from the SVV_EXTERNAL_PARTITIONS table from Amazon Redshift directly. Refer to the Amazon Redshift documentation for more details about the schema and usage.

ExternalSchemas

This table contains information from the SVV_EXTERNAL_SCHEMAS table from Amazon Redshift directly. Refer to the Amazon Redshift documentation for more details about the schema and usage.

ExternalTables

This table contains information from the SVV_EXTERNAL_TABLES table from Amazon Redshift directly. Refer to the Amazon Redshift documentation for more details about the schema and usage.

Functions

This table contains information from the PG_PROC table from Amazon Redshift directly. Refer to the Amazon Redshift and PostgreSQL documentation for more details about the schema and usage.

Queries

This table is generated using the information from the QueryLogs table. Unlike the QueryLogs table, every row in the Queries table contains only one query statement stored in the QueryText column. This table provides the source data to generate the Statistics tables and translation outputs.

QueryLogs

This table provides information on query execution.

QueryTypeStatistics

TableInfo

This table contains information extracted from the SVV_TABLE_INFO table in Amazon Redshift.

TableRelations

TableSizes

This table provides information on tables sizes.

Tables

This table contains information extracted from the SVV_TABLES table in Amazon Redshift. Refer to the Amazon Redshift documentation for more details about the schema and usage.

TranslatedQueries

This table provides query translations.

TranslationErrors

This table provides information about query translation errors.

UserTableRelations

Users

This table contains information extracted from the PG_USER table in Amazon Redshift. Refer to the PostgreSQL documentation for more details about the schema and usage.

Apache Hive

Columns

This table provides information about the columns:

CreateAndDropStatistic

This table provides information on the creation and deletion of tables:

Databases

This table provides information about the databases:

Functions

This table provides information about the functions:

ObjectReferences

This table provides information about the objects referenced in queries:

ParititionKeys

This table provides information about the partition keys:

Parititions

This table provides information about the tables partitions:

Queries

This table is generated using the information from the QueryLogs table. Unlike the QueryLogs table, every row in the Queries table contains only one query statement stored in the QueryText column. This table provides the source data to generate the Statistics tables and translation outputs:

QueryLogs

This table provides some execution statistics about the extracted queries:

QueryTypeStatistics

This table provides statistics on types of queries:

QueryTypes

This table provides statistics on types of queries:

SchemaConversion

This table provides information about schema conversions that are related to clustering and partitioning:

TableRelations

This table provides information about tables:

TableSizes

This table provides information about sizes of tables:

Tables

This table provides information about tables:

TranslatedQueries

This table provides query translations:

TranslationErrors

This table provides information about query translation errors:

UserTableRelations

Snowflake

Warehouses

Databases

Schemata

Tables

Columns

CreateAndDropStatistics

Queries

QueryLogs

QueryTypeStatistics

TableRelations

TranslatedQueries

TranslationErrors

UserTableRelations