## Troubleshooting Google BigQuery connectivity

URL: https://docs.atlan.com/apps/connectors/data-warehouses/google-bigquery/troubleshooting/troubleshooting-google-bigquery-connectivity

> Learn about troubleshooting google bigquery connectivity.

#### Does Atlan support nested columns beyond level 15?

Atlan supports nested columns up to level 15, because BigQuery’s maximum supported nested (RECORD) depth is 15 levels. For more information, see [the official BigQuery documentation on nested and repeated columns](https://docs.cloud.google.com/bigquery/docs/nested-repeated#limitations).

#### What are the known limitations of generating lineage for nested columns?

The following examples illustrate the known limitations of generating lineage for Google BigQuery nested columns in Atlan:

- Lineage will not be generated if you use the nested column of a `RECORD` in the source table or view to create another nested column for a `RECORD REPEATED` column in the target table or view. For example:

 ```sql
 CREATE VIEW orders_view AS
 SELECT
 ARRAY(SELECT AS STRUCT 
 customer.name AS customer_name,
 order_id AS order_id
 ) AS orders
 FROM
 orders_raw;
 ```

 - Lineage will be generated for: `orders_raw.order_id` → `orders_view.orders.order_id`
 - Lineage will not be generated for: `orders_raw.customer.name` → `orders_view.orders.customer_name`
- Lineage will not be generated for a nested column if you use the `RECORD` column as well as a level 1 nested column in the source table or view to create a target column in another table or view. For example:

 ```sql
 CREATE VIEW orders_view AS
 SELECT
 customer AS customer;
 customer.name AS customer_name,
 order_id AS order_id
 FROM
 orders_raw;
 ```

 - Lineage will be generated for:
 - `orders_raw.customer` → `orders_view.customer`
 - `orders_raw.customer.address.city` → `orders_view.city`
 - Lineage will not be generated for: `orders_raw.customer.name` → `orders_view.customer_name`. Instead, lineage will be generated between `orders_raw.customer` → `orders_view.customer_name`.
- Lineage will not be generated for a nested column if you use a table alias to refer to nested columns in a source table or view to create a target column in another table or view. For example:

 ```sql
 CREATE VIEW orders_view AS
 SELECT
 o.order_id AS order_id
 o.customer.name as customer_name
 o.customer.address.city as city
 FROM
 orders_raw o;
 ```

 - Lineage will be generated for: `orders_raw.order_id` → `orders_view.order_id`
 - Lineage will not be generated for:
 - `orders_raw.customer.name` → `orders_view.customer_name`
 - `orders_raw.customer.address.city` → `orders_view.city`
- Lineage will not be generated for a nested column if you use a source table or view `CROSS JOIN` with `UNNEST` to create a target table or view. For example:

 ```sql
 CREATE VIEW orders_view AS
 SELECT
 order_id,
 customer.name as customer_name,
 customer.address.city as city,
 i.item_name AS item_name
 FROM
 orders_raw CROSS JOIN UNNEST(items) AS i;
 ```

 - Lineage will be generated for: `orders_raw.order_id` → `orders_view.order_id`
 - Lineage will not be generated for:
 - `orders_raw.customer.name` → `orders_view.customer_name`
 - `orders_raw.customer.address.city` → `orders_view.city`
 - `orders_raw.items.item_name` → `orders_view.item_name`

#### How to debug test authentication and preflight check errors?

**Invalid project ID**

`Provided GCP project ID is invalid, please check and try again.`

- Ensure that the project ID is non-empty and matches the expected project in your Google Cloud console.

**Invalid service account JSON**

Following are the possible error messages for this issue:

`Provided Service account JSON is invalid, please check and try again.`

`Private key in the service account JSON is invalid, please check and try again.`

`Failed to sign service account access token request with the provided private key. Check the service account JSON and try again.`

`Malformed JSON, please check and try again.`

- These indicate issues with the service account JSON key, possibly invalid or malformed data, or incorrect private key or formatting.
- Verify that the service account JSON is correctly formatted, and the private key is correctly specified.
- [Regenerate the service account key](https://docs.atlan.com/llms/connectors/google-bigquery/set-up-google-bigquery/llms.txt) if needed, and ensure that all required fields are included.
- Ensure that the file is not corrupted and follows a proper JSON structure.

**Insufficient permissions**

`Service account doesn't have permission to create jobs, please ensure that the service account has the 'bigquery.jobs.create' permission.`

- Ensure that you have assigned the `bigquery.jobs.create` [permission](https://docs.atlan.com/llms/connectors/google-bigquery/set-up-google-bigquery/llms.txt) to the service account.
- Review the [roles and permissions](https://docs.atlan.com/llms/connectors/google-bigquery/set-up-google-bigquery/llms.txt) assigned to the service account in your Google Cloud IAM settings.

**Cloud Resource Manager API disabled**

`Cloud Resource Manager API has not been used in the configured project before or it is disabled. Please enable it and try again after some time.`

- Open your Google Cloud console and [enable the Cloud Resource Manager API](https://cloud.google.com/endpoints/docs/openapi/enable-api) for the project.
- Wait for the API to be fully activated before retrying the operation.

**Invalid grant, service account not found**

`Unable to get access token for the provided service account, ensure the service account is active and try again.`

- The service account is either inactive or does not exist.
- Ensure that the [service account](https://docs.atlan.com/llms/connectors/google-bigquery/set-up-google-bigquery/llms.txt) you created still exists in your Google Cloud console and has neither been disabled nor deleted.

**General connection failure**

Following are the possible error messages for this issue:

`Unable to connect to the configured BigQuery instance, please check your credentials and configs and then try again.`

`Cannot create poolable connection factory.`

- These indicate a general connection failure to your Google BigQuery instance, possibly due to misconfigured credentials or network issues.
- Verify that your credentials are correctly configured.
- Ensure that there are no network issues blocking the connection.
- If the problem still persists after verifying all of the above, [contact Atlan support](https://docs.atlan.com/support/submit-request).

---