How to insert data into a global variable

Solved!
ls
Level 2
How to insert data into a global variable

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.

0 Kudos
1 Solution
Mattsco
Dataiker

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)

 

Mattsco

View solution in original post

2 Replies
Mattsco
Dataiker

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)

 

Mattsco
ls
Level 2
Author

Thank you so much @Mattsco

0 Kudos