3.6 Database preview - DB Sampling error (Teradata)

The DB Sampling node does not work on Teradata connections, because Teradata doesn’t support LIMIT predicate.

The following SQL is generated:

SELECT *
FROM
    (SELECT *
    FROM
        (SELECT * FROM "prod_exp_dl_mor"."rda_budget"
        ) tempTable_3016780001436587905 LIMIT 100
    ) AS "tempTable_4600643422147233621"
WHERE
    1 = 0;

Which throws this error on execution:

Execute failed: [Teradata Database] [TeraJDBC 15.10.00.35] [Error 3707] [SQLState 42000] Syntax error, expected something like an ‘EXCEPT’ keyword or an ‘UNION’ keyword or a ‘MINUS’ keyword between the word ‘tempTable_5709310247019470894’ and the ‘LIMIT’ keyword.

If I refactor the code this way, it works as required:

SELECT TOP 100 *
FROM
    (SELECT *
    FROM
        (SELECT * FROM "prod_exp_dl_mor"."rda_budget"
        ) tempTable_3016780001436587905 
    ) AS "tempTable_4600643422147233621"
WHERE
    1 = 1;

EDIT: This is for the database nodes, and not the preview nodes. I’ll look into how to accomplish this with the DB nodes.

You can adjust the database dialect that KNIME uses to generate the SQL.

Althought Teradata is not in the list, try using SQL Server as the database type, and it should generate a top instead of a limit.

To change the type, edit the database connection node, click on the advanced tab, and choose the database type from the list.

I haven’t tested with a TD instance yet, but with other databases I can get it to generate the top command. Please try it out.

1 Like

With the new DB nodes, you can set the “Database type” when you add the TD jdbc driver. By choosing SQL Server, it generates the TOP N command versus the LIMIT command.

Thank you for the tip, I tried that (added Teradata driver as a new database in preferences) and it didn’t work.
Same error, same SQL generated.

KNIME%20DB%20Preview%20-%20Teradata%20as%20SQL%20Server%20driver%20setup

SELECT *
FROM
     (SELECT *
     FROM
          (SELECT *
          FROM
               "prod_exp_dl_mor"."rda_budget"
          ) tempTable_8973060580427860775 LIMIT 100
     ) AS "tempTable_5576036503304627987"
WHERE
     1 = 0;

I’m having the same issues as original poster. I’m running 4.0 analytics platform and just tried to use the DB Connecter-> DB Table Selector nodes.

I tried updating the preferences->KNIME->databases->Teradata to “sqlserver” (as did OP) and get same error when trying to preview.

Error during fetching data from the database: java.sql.SQLException: [Teradata Database] [TeraJDBC 16.20.00.12] [Error 3706] [SQLState 42000] Syntax error: expected something between the word ‘tempTable_6290885396463726321’ and the ‘LIMIT’ keyword.

Also, I’d like to point out that DB Connector node looks much different in KNIME 4.0 and so the “sqlserver” selection does not exist that I can find.

Austin, I was able to get the sampling node to work with Teradata and v4.0. First edit your Database Teradata driver preferences settings and select mssqlserver as the database type and specify your Teradata URL and Teradata driver directory. Then in the DB connector select MicroSoft SQL Server as the type and dialect, but select your Teradata driver under driver name, and use your Teradata URL.

1 Like