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

dbengineer516
New Contributor III

‎05-17-2024 08:40 AM

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!

0 Kudos

brockb
Databricks Employee
Databricks Employee

‎05-17-2024 04:08 PM

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.

0 Kudos

dbengineer516
New Contributor III
In response to brockb

‎05-20-2024 07:02 AM

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!
0 Kudos

dbengineer516
New Contributor III
In response to brockb

‎05-20-2024 07:58 AM

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!