Discover this year's submissions to the Dataiku Frontrunner Awards and give kudos to your favorite use cases and success stories!READ MORE

Querying multiple db's in same query with SQLExecutor2

ckilduff
Level 1
Querying multiple db's in same query with SQLExecutor2

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)

 
0 Kudos
1 Reply
AlexT
Dataiker
Dataiker
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.



0 Kudos

Labels

?
Labels (3)
A banner prompting to get Dataiku