3.6 Database preview - DB Sampling error (Teradata)


#1

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;

#2

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.


#3

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.


#4

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;