Scheduling Queries linkback isn't work

[mikhail-moro]

I tried to add query scheduling using AirFlow using this instruction (https://superset.apache.org/docs/configuration/alerts-reports/#scheduling-queries-as-reports ), I change the "linkback" key in the SCHEDULED_QUERIES object, but there are no requests for this link where I click "submit". Perhaps this is a misunderstanding, I thought that SCHEDULED_QUERIES['backlink'] is the link that Superset will use to send the request with data after submitting the form.

[dosubot[bot]]

@mikhail-moro Hello there! I'm a bot here to help you with bug resolutions, questions, and becoming a contributor. Let's tackle this issue together while we wait for a human maintainer.

The SCHEDULED_QUERIES['linkback'] URL in Superset is not used to send a request after submitting the form. Instead, it provides a way to navigate to the scheduler's interface (e.g., Airflow) to view the status or details of the scheduled query.

To properly configure it, ensure that the linkback URL is correctly set in your superset_config.py file. Here is an example configuration:

SCHEDULED_QUERIES = {
    'JSONSCHEMA': {
        'title': 'Schedule',
        'description': (
            'In order to schedule a query, you need to specify when it '
            'should start running, when it should stop running, and how '
            'often it should run. You can also optionally specify '
            'dependencies that should be met before the query is '
            'executed. Please read the documentation for best practices '
            'and more information on how to specify dependencies.'
        ),
        'type': 'object',
        'properties': {
            'output_table': {
                'type': 'string',
                'title': 'Output table name',
            },
            'start_date': {
                'type': 'string',
                'title': 'Start date',
                'format': 'date-time',
                'default': 'tomorrow at 9am',
            },
            'end_date': {
                'type': 'string',
                'title': 'End date',
                'format': 'date-time',
                'default': '9am in 30 days',
            },
            'schedule_interval': {
                'type': 'string',
                'title': 'Schedule interval',
            },
            'dependencies': {
                'type': 'array',
                'title': 'Dependencies',
                'items': {
                    'type': 'string',
                },
            },
        },
    },
    'UISCHEMA': {
        'schedule_interval': {
            'ui:placeholder': '@daily, @weekly, etc.',
        },
        'dependencies': {
            'ui:help': (
                'Check the documentation for the correct format when '
                'defining dependencies.'
            ),
        },
    },
    'VALIDATION': [
        {
            'name': 'less_equal',
            'arguments': ['start_date', 'end_date'],
            'message': 'End date cannot be before start date',
            'container': 'end_date',
        },
    ],
    'linkback': (
        'https://airflow.example.com/admin/airflow/tree?'
        'dag_id=query_${id}_${extra_json.schedule_info.output_table}'
    ),
}

This configuration will add a "Schedule" menu item to SQL Lab, allowing users to specify scheduling details for their queries. The linkback URL is used to generate a link to the Airflow interface, where the scheduled query can be managed.

To apply this configuration, ensure that your superset_config.py file is correctly set up and referenced by Superset. You can do this by setting the SUPERSET_CONFIG_PATH environment variable to the path of your superset_config.py file. For example:

export SUPERSET_CONFIG_PATH=/app/superset_config.py

If you are using Docker, you can add your configuration file to the container and set the environment variable as follows:

COPY --chown=superset superset_config.py /app/
ENV SUPERSET_CONFIG_PATH /app/superset_config.py

Make sure to restart your Superset instance after making these changes to apply the new configuration.

Additionally, the ScheduleQueryButton component in Superset's frontend handles the form submission and scheduling logic. When the form is submitted, it triggers the onScheduleSubmit function, which packages the query details and scheduling information into a request. This request is then sent to the backend to schedule the query. The linkback URL is not involved in this process but is used to provide a link to the scheduler's interface for managing the scheduled query ^[1][2][3].

To continue talking to Dosu, mention @dosu.

[mikhail-moro]

@dosu I've checked the onScheduleSubmit function, but I can't find the logic that "provides a way to navigate to the scheduler's interface (e.g., Airflow) to view the status or details of the scheduled query" using a linkback'. I'm not good at JS, so maybe I missed something.

[dosubot[bot]]

The onScheduleSubmit function itself does not handle the navigation to the scheduler's interface. Instead, the linkback URL in the SCHEDULED_QUERIES configuration is used to generate a link that users can follow to view the status or details of the scheduled query in the scheduler's interface (e.g., Airflow).

Here's a breakdown of how it works:

1. Configuration: The linkback URL is defined in the SCHEDULED_QUERIES configuration in your superset_config.py file. This URL is constructed with placeholders that are replaced with actual query details when the link is generated.

   SCHEDULED_QUERIES = {
       'linkback': (
           'https://airflow.example.com/admin/airflow/tree?'
           'dag_id=query_${id}_${extra_json.schedule_info.output_table}'
       ),
   }

2. Form Submission: When the form is submitted, the onScheduleSubmit function packages the query details and scheduling information into a request. This request is then sent to the backend to schedule the query.

   const onScheduleSubmit = ({
     formData,
   }: {
     formData: Omit<FormProps<Record<string, any>>, 'schema'>;
   }) => {
     const query = {
       label,
       description,
       db_id: dbId,
       schema,
       sql,
       extra_json: JSON.stringify({ schedule_info: formData }),
     };
     onSchedule(query);
     saveModal?.current?.close();
   };

3. Link Generation: The linkback URL is not directly involved in the form submission process. Instead, it is used to generate a link that users can follow to view the scheduled query in the scheduler's interface. This link is typically displayed in the UI where users can access it after the query has been scheduled.

To summarize, the linkback URL is used to create a link to the scheduler's interface, but it is not part of the form submission logic in the onScheduleSubmit function. Instead, it provides a way for users to navigate to the scheduler's interface after the query has been scheduled ^[1].

To continue talking to Dosu, mention @dosu.

[mikhail-moro]

@dosu so, as far as I know, Superset haven't functional for query scheduling, and the documentation suggests using AirFlow. I don't quite understand how I could create new DAG for query scheduling. As it is written in the docs: "This information can then be retrieved from the endpoint /api/v1/saved_query/ and used to schedule the queries that have schedule_info in their JSON metadata", does this mean that I have to send requests to /api/v1/saved_query/ from time to time and create new DAGs if there are new saved requests?

[dosubot[bot]]

Yes, you need to periodically send requests to /api/v1/saved_query/ to check for new saved queries and then create new DAGs in Airflow if there are new scheduled queries.

Here is an example of how you can implement this in Python:

import requests
import json
from airflow import DAG
from airflow.operators.python_operator import PythonOperator
from datetime import datetime, timedelta

# Function to fetch saved queries
def fetch_saved_queries():
    url = "http://your-superset-instance/api/v1/saved_query/"
    headers = {
        "Authorization": "Bearer YOUR_ACCESS_TOKEN"
    }
    response = requests.get(url, headers=headers)
    if response.status_code == 200:
        return response.json()
    else:
        response.raise_for_status()

# Function to create a new DAG for each scheduled query
def create_dag(dag_id, schedule_interval, query):
    default_args = {
        'owner': 'airflow',
        'depends_on_past': False,
        'start_date': datetime(2023, 1, 1),
        'email_on_failure': False,
        'email_on_retry': False,
        'retries': 1,
        'retry_delay': timedelta(minutes=5),
    }

    dag = DAG(
        dag_id,
        default_args=default_args,
        description='A DAG for scheduled query',
        schedule_interval=schedule_interval,
    )

    def run_query():
        # Logic to run the query
        pass

    run_query_task = PythonOperator(
        task_id='run_query',
        python_callable=run_query,
        dag=dag,
    )

    return dag

# Main function to check for new scheduled queries and create DAGs
def check_and_create_dags():
    saved_queries = fetch_saved_queries()
    for query in saved_queries['result']:
        if 'schedule_info' in query['extra_json']:
            schedule_info = query['extra_json']['schedule_info']
            dag_id = f"query_{query['id']}_{schedule_info['output_table']}"
            schedule_interval = schedule_info['schedule_interval']
            globals()[dag_id] = create_dag(dag_id, schedule_interval, query)

# Schedule the main function to run periodically
default_args = {
    'owner': 'airflow',
    'depends_on_past': False,
    'start_date': datetime(2023, 1, 1),
    'email_on_failure': False,
    'email_on_retry': False,
    'retries': 1,
    'retry_delay': timedelta(minutes=5),
}

dag = DAG(
    'check_and_create_dags',
    default_args=default_args,
    description='A DAG to check for new scheduled queries and create DAGs',
    schedule_interval=timedelta(minutes=10),
)

check_and_create_dags_task = PythonOperator(
    task_id='check_and_create_dags',
    python_callable=check_and_create_dags,
    dag=dag,
)

This script periodically checks for new saved queries and creates new DAGs in Airflow if there are new scheduled queries. Adjust the url, headers, and other parameters as needed for your specific setup ^[1][2].

To continue talking to Dosu, mention @dosu.