UPDATE with MERGE INTO and Temporary View - Execute Multiple Queries Statements

I need to join some tables to update data, and seems that the best approach to do it in Databricks is with MERGE INTO. However, it works fine in Databricks UI, but with Node JS connector it doesn't work because has multiples queries statements (divided by semicolons ";"). It's like this:

DROP VIEW IF EXISTS tempView; 

CREATE TEMPORARY VIEW tempView AS
    SELECT ... FROM table1 t1 JOIN... WHERE xyz="..." ;

MERGE INTO table2 t2 USING tempView ON t1.id = t2.id
 WHEN MATCHED THEN 
     UPDATE SET
         column1 = ..., 
         column2 = ...

How can I execute this query with NodeJS connector?

@renatojmsantos

Hi @renatojmsantos! Just execute your queries sequentially within the same session:

const session = await client.openSession();

await session.executeStatement('DROP VIEW IF EXISTS tempView');
await session.executeStatement('CREATE TEMPORARY VIEW tempView ...');
await session.executeStatement('MERGE INTO table2 t2 USING tempView ...');

DBSQL UI internally does it in exactly the same way

@kravets-levko

Hi @kravets-levko! Thank for your help. And how can I handle with queryOperation to after fetchAll()?

And how can I handle with queryOperation to after fetchAll()?

I'm not sure I understand the question, but will try to answer anyway 😅

Each await session.executeStatement returns IOperation object which can be used to fetch data returned by query. If you just want to wait for query completion, you may wait for finished, like this:

const op = await session.executeStatement('DROP VIEW IF EXISTS tempView');
await op.finished();
// once previous query has completed execution, you can safely run next query

Or if you want to get rows returned by a particular query, use fetchAll:

const op = await session.executeStatement('MERGE INTO table2 t2 USING tempView ...');
const result = await op.fetchAll();

All relevant IOperation methods (fetchAll/fetchChunk/getSchema and, of course, finished) will wait for query completion.

So just do it as you'd do for executing a single query, the idea is to run all three queries within the same session, so they share the same context.

Thank you so much for your reply. I will try this way.

@kravets-levko

Hi @kravets-levko! This works, but the issue that I have is related to have some relationships / dependencies between these multiple queries.

For example:

If my first query is like: CREATE TEMPORARY VIEW tempView AS SELECT ... FROM table1 t1 JOIN... WHERE xyz="..." ; ...
And seconds query like: select * from tempView

When it comes the second query, it doesn't know what is the tempView...

@renatojmsantos

@renatojmsantos can you show your code please?

@kravets-levko

Hi @kravets-levko, it's like this:

const queryDatabricks = async (queryStatement: string) => {
 
  const client = new DBSQLClient();
 
  let result = "";
 
  await client
      .connect({
            token: token,
            host:  serverHostname,
            path:  httpPath
      })
      .then(async (client: any) => {
 
        const session = await client.openSession();
       
        const queries = queryStatement.replace(/\r?\n|\r?\s+/g, " ").trim().split(";");
       
        await Promise.all(queries.map(async query => {
        
          query += "; ";
 
          const op = await session.executeStatement(
              query,
              { runAsync: true }
          );
 
          result = await op.fetchAll();
       
          await op.close();
       
        }));
 
        await session.close();
        await client.close();
 
      })
      .catch((error) => {
         
        console.log(error);
 
      });
 
  return result;
 
};

With Promise.all your queries will be executed in parallel, basically, almost simultaneously. So it's very likely that SELECT will be execute before CREATE TEMPORARY VIEW. You need to ensure that you execute queries sequentially:

async function runQuery(session, query) {
  const op = await session.executeStatement(query, { runAsync: true });
  result = await op.fetchAll();
  await op.close();
  return result;
}

await client
  .connect({ ... })
  .then(async (client: any) => {
    const session = await client.openSession();
  
    const queries = queryStatement.replace(/\r?\n|\r?\s+/g, " ").trim().split(";");
    for (const query of queries) {
      const result = await runQuery(session, query);
      // do something with result
    }

    await session.close();
  });

Thank you so much! This worked and solved my issue. Have a great day!

@renatojmsantos

Thank you @renatojmsantos! I'm glad it worked out!

Navigation Menu

Repository navigation

UPDATE with MERGE INTO and Temporary View - Execute Multiple Queries Statements #98

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

UPDATE with MERGE INTO and Temporary View - Execute Multiple Queries Statements #98

Description

Activity

kravets-levko commented on Dec 15, 2022

renatojmsantos commented on Dec 15, 2022

kravets-levko commented on Dec 15, 2022

renatojmsantos commented on Dec 17, 2022

renatojmsantos commented on Dec 20, 2022

kravets-levko commented on Dec 20, 2022

renatojmsantos commented on Dec 20, 2022

kravets-levko commented on Dec 20, 2022

renatojmsantos commented on Dec 21, 2022

kravets-levko commented on Dec 21, 2022

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions