BigQuery Metadata Ingestion Issue

[mtseafood]

Background

I'm following the OpenMetadata documentation to perform external ingestion from my client's BigQuery instance. I'm using default Application Default Credentials (ADC) and can successfully ingest metadata into OpenMetadata when the BigQuery table is in the default project.

Goal

I want to ingest metadata from a different project: gdw-prod-data. I've verified that the default ADC account can access the table schema and related information in this project.

Configuration

source:
  type: bigquery
  serviceName: bigquery-poc
  serviceConnection:
    config:
      type: BigQuery
      credentials:
        gcpConfig: {}
  sourceConfig:
    config:
      type: DatabaseMetadata
      markDeletedTables: true
      markDeletedStoredProcedures: true
      includeTables: true
      includeViews: true
      databaseFilterPattern:
        includes:
          - gdw-prod-data
      schemaFilterPattern:
        includes:
          - 
sink:
  type: metadata-rest
  config: {}
workflowConfig:
  loggerLevel: DEBUG
  openMetadataServerConfig:
    hostPort: "https://{my host}/api"
    authProvider: openmetadata
    securityConfig:
      jwtToken: "xxxx"
    storeServiceConnection: true

Issue

In the ingestion logs, I noticed that the Fully Qualified Name (FQN) is different from what I expected:

• What I got: bigquery-poc.gdw-dev-myproject
• What I want to see: bigquery-poc.gdw-prod-data

Log Snippet

[2024-09-23 05:23:41] INFO     {metadata.Utils:logger:178} - BigQuery Status:
[2024-09-23 05:23:41] INFO     {metadata.Utils:logger:178} - {'failures': [],
 'filtered': [{'bigquery-poc.gdw-dev-myproject': 'Database Filtered out'}],
 'records': [],
 'source_start_time': 1727068995.4430978,
 'updated_records': [],
 'warnings': []}

Question

How can I modify my configuration to ensure that the metadata is ingested from the correct project (gdw-prod-data) and the FQN reflects this?

[Prajwal214]

Under the database filter you can add the pattern of database which you want to include in the ingestion. In your case you can refer the below

databaseFilterPattern:
        includes:
          - gdw-dev-myproject

[mtseafood]

Thanks @Prajwal214 for the feedback. I tried using the following YAML file:

YAML

source:
  type: bigquery
  serviceName: bigquery-poc
  serviceConnection:
    config:
      type: BigQuery
      credentials:
        gcpConfig:
          type: service_account
          projectId: gdw-dev-myproject;gdw-prod-data

Part of the error:

metadata.ingestion.connections.test_connections.SourceConnectionException: Some steps failed when testing the connection: [failed=['\'CheckAccess\': This is a mandatory step and we won\'t be able to extract necessary metadata. Failed due to: (google.cloud.bigquery.dbapi.exceptions.DatabaseError) 400 POST https://bigquery.googleapis.com/bigquery/v2/projects/gdw-dev-myproject;gdw-prod-data/queries?prettyPrint=false: Cannot parse  as CloudRegion.
[SQL: /* {"app": "OpenMetadata", "version": "1.5.5.0"} */
SELECT SESSION_USER()]
(Background on this error at: https://sqlalche.me/e/14/4xp6)'] success=[] warning=[]]

[2024-09-26 14:11:03] ERROR    {metadata.Utils:logger:178} - 
Error initializing metadata: Some steps failed when testing the connection: [failed=['\'CheckAccess\': This is a mandatory step and we won\'t be able to extract necessary metadata. Failed due to: (google.cloud.bigquery.dbapi.exceptions.DatabaseError) 400 POST https://bigquery.googleapis.com/bigquery/v2/projects/gdw-dev-myproject;gdw-prod-data/queries?prettyPrint=false: Cannot parse  as CloudRegion.
[SQL: /* {"app": "OpenMetadata", "version": "1.5.5.0"} */
SELECT SESSION_USER()]
(Background on this error at: https://sqlalche.me/e/14/4xp6)'] success=[] warning=[]]

Follow up

From the error, it is clear that my default credential cannot use SELECT SESSION_USER() in the gdw-prod-data project. This restriction is set up by our organization; we cannot query data directly on the shared project (gdw-prod-data).

However, if we are in the solution project (gdw-dev-myproject), we can query the data from it. Is there any workaround we can use for this case?

[Prajwal214]

@mtseafood The Ingestion would require the permission for Default Credentials for Ingestion to work. You can use multiple project ids but the mentioned requirements need to in place for the ingestion.

BigQuery Requirements

[mtseafood]

Thanks @Prajwal214 for the help!