topic Re: /api/2.0/preview/sql/queries API only returning certain queries in Data Engineering

/api/2.0/preview/sql/queries API only returning certain queries

dbengineer516 — Fri, 17 May 2024 15:40:18 GMT

Hello,

When using /api/2.0/preview/sql/queries to list out all available queries, I noticed that certain queries were being shown while others were not. I did a small test on my home workspace, and it was able to recognize certain queries when I defined a function to take in a parent folder. The goal was to be able to see all queries within a specified "parent," but the API call only returned back under half of the query files I expected in the parent/subfolders under the parent. Is there a reason why some query files are shown while others are not? Please advise.

Thank you!

Re: /api/2.0/preview/sql/queries API only returning certain queries

brockb — Fri, 17 May 2024 23:08:40 GMT

Hi,

How many queries were returned in the API call in question? The List Queries documentation describes this endpoint as supporting pagination with a default page size of 25, is that how many you saw returned?

Query parameters page_size integer <= 1000 Default 25 Number of queries to return per page.

Thanks.

Re: /api/2.0/preview/sql/queries API only returning certain queries

dbengineer516 — Mon, 20 May 2024 14:02:57 GMT

Hi @brockb,

The count of queries I got returned from this call was 732. What I tried to accomplish was being able to view all queries under a certain parent folder. See code below:

import requests

import json

def get_queries_in_parent(parent):

# Define the necessary parameters

sql_api_url = '<workspace_url>/api/2.0/preview/sql/queries' #workspace_url replaced with my workspace url

api_token = '<token>' #token replaced with my actual access token

# Set up the headers with the API token

headers = {

'Authorization': f'Bearer {api_token}',

'Content-Type': 'application/json'

}

# Make the GET request to the SQL API

response = requests.get(sql_api_url, headers=headers)

# Extract and print the list of queries

if response.status_code == 200:

response_json = response.json()

print(response_json)

queries = response_json.get('results', [])

for query in queries:

query_id = query.get('id')

query_name = query.get('name')

query_text = query.get('query')

query_parent = query.get('options', {}).get('parent')

if query_parent == parent:

print(f"Query ID: {query_id}, Name: {query_name}, Query Text: {query_text}, Parent: {query_parent}")

else:

print(f'Request failed with status code: {response.status_code}')

print(response.json())

# Specify the parent workspace folder

parent_folder = 'folders/123456789123' # example parent folder

# Call the function to get queries in the specified parent workspace folder

get_queries_in_parent(parent_folder)

I also searched by id and name to see if certain queries even existed in my list, and a good handful of queries were not in the original list.

Thank you!

Re: /api/2.0/preview/sql/queries API only returning certain queries

dbengineer516 — Mon, 20 May 2024 14:58:27 GMT

Hi @brockb,

You were correct. I was only grabbing the first page of results, so I updated my code to traverse each page to grab all queries and add each page's results into a list.

Thank you!