InvalidOperation GetMetadata Error While Scanning Snowflake in Microsoft Purview

Question

InvalidOperation GetMetadata Error While Scanning Snowflake in Microsoft Purview

Max Harper 0

When scanning a Snowflake database for Microsoft Purview using Scan Level Auto-Detect, an invalidOperation error occurs for tables. The error is specifically related to the GetMetaData operation. The following query returns no results when attempting to scan my TEST_TABLE in the TEST schema:

SELECT T.TABLE_CATALOG, T.TABLE_SCHEMA, T.TABLE_NAME, T.TABLE_TYPE, T.COMMENT, V.IS_SECURE, T.LAST_ALTERED 
FROM INFORMATION_SCHEMA.TABLES T 
LEFT OUTER JOIN INFORMATION_SCHEMA.VIEWS V ON (T.TABLE_CATALOG=V.TABLE_CATALOG AND T.TABLE_SCHEMA=V.TABLE_SCHEMA AND T.TABLE_NAME=V.TABLE_NAME) 
WHERE T.TABLE_TYPE IN ('BASE TABLE') AND T.TABLE_NAME ='null' AND T.TABLE_SCHEMA ='TEST'.

The issue appears to be that the automated query incorrectly uses T.TABLE_NAME ='null', which results in an empty query. Is the automated query supposed to have the 'null' as TEST_TABLE, if not, how do I fix this problem?

phemanth 14,800 Reputation points Microsoft External Staff

2024-12-02T20:04:36.0533333+00:00
@Max Harper

Thanks for reaching out to Microsoft Q&A.

It looks like the issue is indeed with the automated query using T.TABLE_NAME = 'null'. This condition will always return no results because there is no table with the name ‘null’. The query should be dynamically inserting the actual table name instead of ‘null’.

To fix this problem, you can manually adjust the query to include the correct table name. For example, if your table name is TEST_TABLE, the query should look like this:

SELECT T.TABLE_CATALOG, T.TABLE_SCHEMA, T.TABLE_NAME, T.TABLE_TYPE, T.COMMENT, V.IS_SECURE, T.LAST_ALTERED FROM INFORMATION_SCHEMA.TABLES T LEFT OUTER JOIN INFORMATION_SCHEMA.VIEWS V ON (T.TABLE_CATALOG=V.TABLE_CATALOG AND T.TABLE_SCHEMA=V.TABLE_SCHEMA AND T.TABLE_NAME=V.TABLE_NAME) WHERE T.TABLE_TYPE IN ('BASE TABLE') AND T.TABLE_NAME = 'TEST_TABLE' AND T.TABLE_SCHEMA = 'TEST';

Configuration Settings: Ensure that the scanning configuration in Microsoft Purview is correctly set up to dynamically insert table names.

Permissions: Verify that the account used for scanning has the necessary permissions to access the metadata in Snowflake.

Logs and Documentation: Review the logs for any additional error messages

If above of these steps resolve the issue, do let us know.
Max Harper 0 Reputation points

2024-12-02T21:09:18.25+00:00

How do I set up configuration settings in Purview so I can dynamically insert table names? When creating a scan it doesn't have that option listed
Max Harper 0 Reputation points

2024-12-02T21:11:19.5866667+00:00

How do I configure the scan so it inserts table names dynamically?

phemanth 14,800 Microsoft External Staff

@Max Harper

To dynamically insert table names in Microsoft Purview when creating a scan, you can use the Purview REST API or the Purview Python SDK.

Using Purview REST API

Register a Service Principal: Ensure you have a service principal with the necessary permissions.
Authenticate: Use the service principal to authenticate against the Purview API.
Create a Scan: Use the API to create a scan and dynamically insert table names.

Install the SDK:

   pip install azure-purview-scanning azure-identity azure-purview-catalog azure-purview-account azure-core

Authenticate: Python

   from azure.identity import ClientSecretCredential
   from azure.purview.scanning import PurviewScanningClient
   client_id = "<YOUR_CLIENT_ID>"
   client_secret = "<YOUR_CLIENT_SECRET>"
   tenant_id = "<YOUR_TENANT_ID>"
   credential = ClientSecretCredential(client_id=client_id, client_secret=client_secret, tenant_id=tenant_id)
   scanning_client = PurviewScanningClient(endpoint="https://<YOUR_PURVIEW_ACCOUNT>.purview.azure.com", credential=credential)

Create and Configure the Scan: Python

   scan_name = "your_scan_name"
   data_source_name = "your_data_source_name"
   scan_payload = {
       "scanRulesetName": "your_scan_ruleset_name",
       "scanLevel": "Full",
       "scanTriggerType": "OnDemand",
       "scanProperties": {
           "scanRulesetType": "System",
           "scanRulesetVersion": "1.0",
           "scanRulesetId": "your_scan_ruleset_id"
       }
   }
   response = scanning_client.scan.create_or_update(data_source_name, scan_name, scan_payload)
   print(response)

Configuration Settings

Ensure Permissions: Verify that the service principal has the necessary roles assigned in Purview.
Dynamic Table Names: Modify the scan configuration to dynamically insert table names based on your requirements.

For detailed steps and examples, you can refer to the Microsoft Purview documentation.

phemanth 14,800 Reputation points Microsoft External Staff

2024-12-04T18:00:46.3166667+00:00

@Max Harper We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Max Harper 0 Reputation points

2024-12-04T18:07:53.4833333+00:00

I'll test and see, thanks!
phemanth 14,800 Reputation points Microsoft External Staff

2024-12-05T19:42:08.58+00:00

@Max Harper was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Max Harper 0 Reputation points

2024-12-05T19:44:13.3366667+00:00

I'll get back to you when when I do testing, thanks!
Kimball 0 Reputation points

2024-12-09T17:59:50.71+00:00

@phemanth can you provide any more details on the permissions that the service principal requires? Can you also specify how the SDK is making any changes to the scan configuration? It doesn't look like it's making any changes to insert table names dynamically. I figure I must be missing something. Thanks in advance.
phemanth 14,800 Reputation points Microsoft External Staff

2024-12-10T15:36:59.86+00:00

@Max Harper We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Your answer

phemanth 14,800 Reputation points Microsoft External Staff

2024-12-02T20:04:36.0533333+00:00

@Max Harper

Thanks for reaching out to Microsoft Q&A.

It looks like the issue is indeed with the automated query using T.TABLE_NAME = 'null'. This condition will always return no results because there is no table with the name ‘null’. The query should be dynamically inserting the actual table name instead of ‘null’.

To fix this problem, you can manually adjust the query to include the correct table name. For example, if your table name is TEST_TABLE, the query should look like this:

SELECT T.TABLE_CATALOG, T.TABLE_SCHEMA, T.TABLE_NAME, T.TABLE_TYPE, T.COMMENT, V.IS_SECURE, T.LAST_ALTERED FROM INFORMATION_SCHEMA.TABLES T LEFT OUTER JOIN INFORMATION_SCHEMA.VIEWS V ON (T.TABLE_CATALOG=V.TABLE_CATALOG AND T.TABLE_SCHEMA=V.TABLE_SCHEMA AND T.TABLE_NAME=V.TABLE_NAME) WHERE T.TABLE_TYPE IN ('BASE TABLE') AND T.TABLE_NAME = 'TEST_TABLE' AND T.TABLE_SCHEMA = 'TEST';

Configuration Settings: Ensure that the scanning configuration in Microsoft Purview is correctly set up to dynamically insert table names.

Permissions: Verify that the account used for scanning has the necessary permissions to access the metadata in Snowflake.

Logs and Documentation: Review the logs for any additional error messages

If above of these steps resolve the issue, do let us know.
Max Harper 0 Reputation points

2024-12-02T21:09:18.25+00:00

How do I set up configuration settings in Purview so I can dynamically insert table names? When creating a scan it doesn't have that option listed
Max Harper 0 Reputation points

2024-12-02T21:11:19.5866667+00:00

How do I configure the scan so it inserts table names dynamically?
phemanth 14,800 Reputation points Microsoft External Staff

2024-12-03T18:53:27.77+00:00

@Max Harper

To dynamically insert table names in Microsoft Purview when creating a scan, you can use the Purview REST API or the Purview Python SDK.

Using Purview REST API

Register a Service Principal: Ensure you have a service principal with the necessary permissions.

Authenticate: Use the service principal to authenticate against the Purview API.

Create a Scan: Use the API to create a scan and dynamically insert table names.

Install the SDK:
pip install azure-purview-scanning azure-identity azure-purview-catalog azure-purview-account azure-core

Authenticate: Python
from azure.identity import ClientSecretCredential from azure.purview.scanning import PurviewScanningClient client_id = "<YOUR_CLIENT_ID>" client_secret = "<YOUR_CLIENT_SECRET>" tenant_id = "<YOUR_TENANT_ID>" credential = ClientSecretCredential(client_id=client_id, client_secret=client_secret, tenant_id=tenant_id) scanning_client = PurviewScanningClient(endpoint="https://<YOUR_PURVIEW_ACCOUNT>.purview.azure.com", credential=credential)

Create and Configure the Scan: Python
scan_name = "your_scan_name" data_source_name = "your_data_source_name" scan_payload = { "scanRulesetName": "your_scan_ruleset_name", "scanLevel": "Full", "scanTriggerType": "OnDemand", "scanProperties": { "scanRulesetType": "System", "scanRulesetVersion": "1.0", "scanRulesetId": "your_scan_ruleset_id" } } response = scanning_client.scan.create_or_update(data_source_name, scan_name, scan_payload) print(response)

Configuration Settings

Ensure Permissions: Verify that the service principal has the necessary roles assigned in Purview.

Dynamic Table Names: Modify the scan configuration to dynamically insert table names based on your requirements.

For detailed steps and examples, you can refer to the Microsoft Purview documentation.
phemanth 14,800 Reputation points Microsoft External Staff

2024-12-04T18:00:46.3166667+00:00

@Max Harper We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Max Harper 0 Reputation points

2024-12-04T18:07:53.4833333+00:00

I'll test and see, thanks!
phemanth 14,800 Reputation points Microsoft External Staff

2024-12-05T19:42:08.58+00:00

@Max Harper was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Max Harper 0 Reputation points

2024-12-05T19:44:13.3366667+00:00

I'll get back to you when when I do testing, thanks!
Kimball 0 Reputation points

2024-12-09T17:59:50.71+00:00

@phemanth can you provide any more details on the permissions that the service principal requires? Can you also specify how the SDK is making any changes to the scan configuration? It doesn't look like it's making any changes to insert table names dynamically. I figure I must be missing something. Thanks in advance.
phemanth 14,800 Reputation points Microsoft External Staff

2024-12-10T15:36:59.86+00:00

@Max Harper We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Share via

InvalidOperation GetMetadata Error While Scanning Snowflake in Microsoft Purview

Your answer