How to insert data into a global variable

Options
ls
ls Registered Posts: 6 ✭✭✭✭

I have a set from a query "select distinct c1 from t1" where it returns 3 records/values, 1,2,1999.

How do I send them to a global variable v1 automatically, like this...

{
"v1": "1, 2, 1999"
}

Thanks very much.

Best Answer

  • Mattsco
    Mattsco Administrator, Dataiker Posts: 125 Administrator
    edited July 17 Answer ✓
    Options

    Hi,

    You can use a python recipe to query your SQL dataset and update the project variables. You need to specify output to the recipe but no need to fill it with data.
    You can try this sample code replacing the input_dataset and the SQL query with yours.

    # -*- coding: utf-8 -*-
    import dataiku
    import pandas as pd, numpy as np
    from dataiku.core.sql import SQLExecutor2
    
    project_key = dataiku.get_custom_variables()["projectKey"]
    input_dataset = "sql_dataset_name"
    
    # Run a sql from python.
    executor = SQLExecutor2(dataset=input_dataset)
    query = 'SELECT count(*), count(*), count(*) \
             FROM "{pk}_{d}"'.format(pk=project_key, d=input_dataset)
    df = executor.query_to_df(query)
    
    # Get the values from the query as a string.
    L = list(df.values[0])
    v1 = ",".join(str(x) for x in L)
    
    # Update the project variables.
    client = dataiku.api_client()
    p = client.get_project(project_key)
    
    var = p.get_variables()
    var["standard"]["v1"] = v1
    p.set_variables(var)
    

Answers

Setup Info
    Tags
      Help me…