Querying multiple db's in same query with SQLExecutor2

Options
ckilduff
ckilduff Registered Posts: 1 ✭✭✭
edited July 16 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

  • Alexandru
    Alexandru Dataiker, Dataiku DSS Core Designer, Dataiku DSS ML Practitioner, Dataiku DSS Adv Designer, Registered Posts: 1,209 Dataiker
    edited July 17
    Options
    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.



Setup Info
    Tags
      Help me…