Querying multiple db's in same query with SQLExecutor2

Registered Posts: 1 ✭✭✭
edited July 2024 in Using Dataiku

Hi,

I am trying to use SQLExecutor2 in python to pull in a dataset from a query. The query uses multiple tables from different databases as the example below. Even though I am specifying the DB in the query I am still getting error 'invalid object name'. Is it possible to query multiple databases and output a dataframe?

Example Query:

from dataiku.core.sql import SQLExecutor2
executor = SQLExecutor2(connection='Database1')
Query = """select *
from Database1.dbo.Table1 a
left join Database2.dbo.Table2 b
on a.column1 = b.column2"""
executor.query_to_df(query= Query)

 

Answers

  • Dataiker, Dataiku DSS Core Designer, Dataiku DSS ML Practitioner, Dataiku DSS Adv Designer, Registered Posts: 1,270 Dataiker
    edited July 2024
    Hi,

    Since the database is actually defined at the connection level and SQLExecutor can only use a single connection this will not work.
    You could use multiple executors for example:
    executor1 = SQLExecutor2(connection='Database1')
    executor2 = SQLExecutor2(connection='Database2')
    Query1 = "select * from table1"
    Query2 = "select * from table2"
    executor1.query_to_df(query =Query1)
    executor2.query_to_df(query =Query2)
    Then join the separate data frames using Pandas. As shown in this example.
    It's also possible to use 2 SQL datasets from 2 different databases and use a Visual Join recipe instead.



Welcome!

It looks like you're new here. Sign in or register to get started.

Welcome!

It looks like you're new here. Sign in or register to get started.