## Running SQL queries on an asset

URL: https://docs.atlan.com/product/capabilities/build-apps/sdks/python/access-control/how-tos/run-queries

> Run SQL queries on assets in Atlan using QueryRequest and the Python SDK (pyatlan). Executes on connected data sources.

# QueryRequest: run queries programmatically

Use `QueryRequest` in the Atlan Python SDK to programmatically execute queries on connected data sources.

To run SQL queries on an asset:

### Java

```java showLineNumbers title="Running SQL query on an asset"
QueryRequest query = QueryRequest.creator( // (1)
 "SELECT * FROM \"PACKAGETYPES\" LIMIT 50;",
 "default/snowflake/1705755637"
 )
 .defaultSchema("RAW.WIDEWORLDIMPORTERS_WAREHOUSE") // (2)
 .build();
QueryResponse response = client.queries.stream(query); // (3)
```

1. To create a minimal query object, use the `QueryRequest` creator method and provide the following arguments:
 - SQL query to run.
 - unique name of the connection to use for the query.
2. You must provide default schema name to use for unqualified objects in the SQL, in the form `DB.SCHEMA`.
3. You can now execute the query using the `stream()` method.

### Python

```python showLineNumbers title="Running SQL query on an asset"
from pyatlan.client.atlan import AtlanClient
from pyatlan.model.query import QueryRequest

client = AtlanClient()

query = QueryRequest( # (1)
 sql='SELECT * FROM "PACKAGETYPES" LIMIT 50;',
 data_source_name="default/snowflake/1705755637",
 default_schema="RAW.WIDEWORLDIMPORTERS_WAREHOUSE",
)
response = client.queries.stream(request=query) # (2)
```

1. To build a query, you need to use the `QueryRequest` and provide the following parameters:
 - `sql`: SQL query to run.
 - `data_source_name`: unique name of the connection to use for the query.
 - `default_schema`: default schema name to use for
 unqualified objects in the SQL, in the form `DB.SCHEMA`.
2. You can now execute the query using the `stream()` method.

### Kotlin

```kotlin showLineNumbers title="Running SQL query on an asset"
val query = QueryRequest.creator( // (1)
 "SELECT * FROM \"PACKAGETYPES\" LIMIT 50;",
 "default/snowflake/1705755637"
 )
 .defaultSchema("RAW.WIDEWORLDIMPORTERS_WAREHOUSE") // (2)
 .build()
val response = client.queries.stream(query) // (3)
```

1. To create a minimal query object, use the `QueryRequest` creator method and provide the following arguments:
 - SQL query to run.
 - unique name of the connection to use for the query.
2. You must provide default schema name to use for unqualified objects in the SQL, in the form `DB.SCHEMA`.
3. You can now execute the query using the `stream()` method.

### Raw REST API

```json showLineNumbers title="POST /api/sql/query/stream"
{
 "sql": "SELECT * FROM \"PACKAGETYPES\" LIMIT 50;", // (1)
 "dataSourceName": "default/snowflake/1705755637",
 "defaultSchema": "RAW.WIDEWORLDIMPORTERS_WAREHOUSE"
}
```

1. You must provide the following properties:
 - `sql`: SQL query to run.
 - `dataSourceName`: unique name of the connection to use for the query.
 - `defaultSchema`: default schema name to use for unqualified objects
 in the SQL, in the form `DB.SCHEMA`.

## Use API token to run queries

You can also grant permission to run SQL queries on an asset using an API token, if you want. (This must be explicitly granted, as it isn't possible by default.) You can even mask certain information through data policies on purposes linked to the API token.

:::warning[API token permissions]

Before executing queries on an asset using an API token, make sure that the [token is linked](https://docs.atlan.com/llms/governance/stewardship/manage-api-tokens/llms.txt) to [a persona with a data policy](https://docs.atlan.com/llms/governance/stewardship/manage-personas/llms.txt) that **permits queries for that specific asset**.
:::

### Java

```java showLineNumbers title="Running SQL query on an asset with API token"
AuthPolicy data = Purpose.createDataPolicy( // (1)
 "Mask the data", // (2)
 purpose.getGuid(), // (3)
 AuthPolicyType.DATA_MASK, // (4)
 null,
 List.of(token.getApiTokenUsername()), // (5)
 false
 )
 .policyMaskType(DataMaskingType.REDACT) // (6)
 .build();
AssetMutationResponse response = client.assets.save(List.of(data), false); // (7)

try (AtlanClient tokenClient = new AtlanClient(client.getBaseUrl(), token.getAttributes().getAccessToken()))
```

1. Use the `createDataPolicy()` method to start building a data policy with the minimal required information.
2. You must give the policy a name.
3. You must provide the GUID of the `Purpose` to attach this policy to.
4. Specify the type of policy (granting, denying or masking the data of assets with the tags in the purpose).
5. Set the policy user to the API `token`.
6. Set the type of masking to `REDACT` to redact the tagged elements in the query response.
7. To then add the policy to the purpose in Atlan, call the `save()` method with the policy object you've built.
8. Create a new `AtlanClient` set up to use the new API token.
9. To create a minimal query object, use the `QueryRequest` creator method and provide the following arguments:
 - SQL query to run.
 - unique name of the connection to use for the query.
10. You must provide default schema name to use for unqualified objects in the SQL, in the form `DB.SCHEMA`.
11. You can now execute the query using the `stream()` method.

### Python

```python showLineNumbers title="Running SQL query on an asset with API token"
from pyatlan.client.atlan import AtlanClient
from pyatlan.model.query import QueryRequest

client = AtlanClient()

data = Purpose.create_data_policy( # (1)
 client=client, # (2)
 name="Mask the data", # (3)
 purpose_id=purpose.guid, # (4)
 policy_type=AuthPolicyType.DATA_MASK, # (5)
 policy_users={f"service-account-{token.client_id}"}, # (6)
 all_users=False, # (7)
)
data.policy_mask_type = DataMaskingType.REDACT # (8)
response = client.asset.save(data) # (9)

token_client = AtlanClient( # (10)
 base_url=client.base_url,
 api_key=token.attributes.access_token
)
query = QueryRequest( # (11)
 sql='SELECT * FROM "PACKAGETYPES" LIMIT 50;',
 data_source_name="default/snowflake/1705755637",
 default_schema="RAW.WIDEWORLDIMPORTERS_WAREHOUSE",
)
response = token_client.queries.stream(request=query) # (12)
```

1. Use the `create_data_policy()` method to start building
a data policy with the minimal required information.
2. You must provide a client instance.
3. You must give the policy a name.
4. You must provide the GUID of the `purpose` to attach this policy to.
5. Specify the type of policy (granting, denying or
masking the data of assets with the tags in the purpose).
6. Set the `policy_users` to the API `token`.
7. Set the `all_users` option to `False` as
this policy is intended specifically for the API token.
8. Set the type of masking to `REDACT`
to redact the tagged elements in the query response.
9. To then add the policy to the purpose in Atlan,
call the `save()` method with the policy object you've built.
10. Create a new client with the API token.
11. To build a query, you need to use the `QueryRequest` and provide the following parameters:
 - `sql`: SQL query to run.
 - `data_source_name`: unique name of the connection to use for the query.
 - `default_schema`: default schema name to use for
 unqualified objects in the SQL, in the form `DB.SCHEMA`.
12. You can now execute the query using the `stream()` method.

### Kotlin

```kotlin showLineNumbers title="Running SQL query on an asset with API token"
val data = Purpose.createDataPolicy( // (1)
 "Mask the data", // (2)
 purpose.getGuid(), // (3)
 AuthPolicyType.DATA_MASK, // (4)
 null,
 List.of(token.getApiTokenUsername()), // (5)
 false
 )
 .policyMaskType(DataMaskingType.REDACT) // (6)
 .build()
val response = client.assets.save(listOf(data), false) // (7)

AtlanClient(client.getBaseUrl(), token.getAttributes().getAccessToken()).use { tokenClient -> // (8)
 val query = QueryRequest.creator( // (9)
 "SELECT * FROM \"PACKAGETYPES\" LIMIT 50;",
 "default/snowflake/1705755637"
 )
 .defaultSchema("RAW.WIDEWORLDIMPORTERS_WAREHOUSE") // (10)
 .build()
 val response = tokenClient.queries.stream(query) // (11)
}
```

1. Use the `createDataPolicy()` method to start building a data policy with the minimal required information.
2. You must give the policy a name.
3. You must provide the GUID of the `Purpose` to attach this policy to.
4. Specify the type of policy (granting, denying or masking the data of assets with the tags in the purpose).
5. Set the policy user to the API `token`.
6. Set the type of masking to `REDACT` to redact the tagged elements in the query response.
7. To then add the policy to the purpose in Atlan, call the `save()` method with the policy object you've built.
8. Create a new `AtlanClient` set up to use the new API token.
9. To create a minimal query object, use the `QueryRequest` creator method and provide the following arguments:
 - SQL query to run.
 - unique name of the connection to use for the query.
10. You must provide default schema name to use for unqualified objects in the SQL, in the form `DB.SCHEMA`.
11. You can now execute the query using the `stream()` method.

### Raw REST API

```json showLineNumbers title="POST /api/sql/query/stream"
{
 "sql": "SELECT * FROM \"PACKAGETYPES\" LIMIT 50;", // (1)
 "dataSourceName": "default/snowflake/1705755637",
 "defaultSchema": "RAW.WIDEWORLDIMPORTERS_WAREHOUSE"
}
```

1. You must provide the following properties:
 - `sql`: SQL query to run.
 - `dataSourceName`: unique name of the connection to use for the query.
 - `defaultSchema`: default schema name to use for
 unqualified objects in the SQL, in the form `DB.SCHEMA`.

:::warning[Policy implementation delay]
Be aware that there is a delay of a few minutes after applying new policies
to the token before they become fully effective. If you run a query immediately
after creating the policy, you may still observe unredacted information until
the policy is fully implemented.
:::

---