Query returns empty result set

[iaxentoi]

Trying to run the example code from https://docs.databricks.com/en/dev-tools/nodejs-sql-driver.html#language-JavaScript
I am using the personal token method but every query returns an empty array.
I also enabled debug level logging and seems to be no error ar anything strange:

{"level":"info","message":"Created DBSQLClient"}
{"level":"info","message":"DBSQLClient: initializing thrift client"}
{"level":"debug","message":"Session created with id: 9ece70bf-xxxxxx"}
Running query
{"level":"debug","message":"Operation created with id: b2cfd491-xxxxx"}
{"level":"debug","message":"Fetched chunk of size: 100000 from operation with id: b2cfd491-xxxxx"}
{"level":"debug","message":"Fetched all data from operation with id: b2cfd491-xxxxx"}
{"level":"debug","message":"Closing operation with id: b2cfd491-xxxxx"}
[] //this is the result
{"level":"debug","message":"Session closed with id: 9ece70bf-xxxxx"}`

Running on MacOS using node v18.18.2.

[kravets-levko]

Hi @iaxentoi! Can you please share your exact script? Thank you!

[iaxentoi]

hi, here is the script:

const { DBSQLClient, DBSQLLogger, LogLevel } = require("@databricks/sql");

const serverHostname = "xxxx";
const httpPath = "xxxx";
const token = "secret";

if (!token || !serverHostname || !httpPath) {
  throw new Error(
    "Cannot find Server Hostname, HTTP Path, or " +
      "personal access token. " +
      "Check the environment variables DATABRICKS_SERVER_HOSTNAME, " +
      "DATABRICKS_HTTP_PATH, and DATABRICKS_TOKEN."
  );
}

const client = new DBSQLClient({
   logger: new DBSQLLogger({level: LogLevel.debug })
});

const connectOptions = {
  token: token,
  host: serverHostname,
  path: httpPath,
};

client
  .connect(connectOptions)
  .then(async (client) => {
    const session = await client.openSession();
    console.log(`Running query`);

    const queryOperation = await session.executeStatement(
      'select "Hello"',
      {
        runAsync: true,
        maxRows: 10000, // This option enables the direct results feature.
      }
    );

    const result = await queryOperation.fetchAll();

    await queryOperation.close();

    console.log(result);

    await session.close();
    await client.close();
  })
  .catch((error) => {
    console.log(error);
  });

Actually when switching to another server it works fine, so might some kind of permissions/access issue.
The connect options seem to be fine, otherwise it would give a 403 response.
So it just fails silently for other reason which I was not able to discover yet.

[iaxentoi]

Hi @kravets-levko, after some investigation this seems to be related to the Databricks runtime version.

• returns empty result set on 9.1 LTS
• returns correct result on 10.4 and 13.3

The processing can be seen on Databricks side and the results to be returned, but that is not picked up by the node client and an empty result set is returned instead.

[kravets-levko]

Hi @iaxentoi! Thank you for you replies. It's really great that you managet to localize the issue - now I know where to start with debugging. At the same time, it's weird because the library should throw an error on unsupported result formats, so very likely there's something wrong there. I'll try to reproduce this behavior and let you know.

Also, can you try other conectors if possible (Python/Go)? I'm curious if thir behavior is the same or different. Thank you!

P.S. Your script looks good, I see nothing wrong with it

[iaxentoi]

Yes, tried with Python and it works fine.

[kravets-levko]

To clarify: Python connector works fine with all DBR versions you mentioned? Nevermind, I've already found the difference between Nodejs and Python connectors. Already working on a fix