Create Dataset with SQL issues

[Trifacta_Alumni]

Hi,

Sorry for the long post. I'm very new to Designer Cloud and I'm trying to create a dataset with a custom SQL that I use in regular Alteryx Designer Desktop SQL Editor and also works in MS SQL Server Manager. I've created the connection to our database and see all my tables but I keep getting the below error message:

Invalid SQL: Table not found - com.trifacta.datasystem.connect.exception.NoSuchTableException

There are 2 tables I'm trying to do an inner join to the main table. I can successfully create 3 separate datasets for each individual table but I'd rather use a custom SQL.

Custom SQL(works in MS sql server manager and Designer SQL Editor):

select T2DCBDATA.dbo.tblClient.ClientID,

T2DCBDATA.dbo.tblClient.SearchName as ClientName,

T2DCBDATA.dbo.tblClientExtraDetails.ClientSortName,

T2DCBDATA.dbo.tblClient.AddressDetail,

T2DCBDATA.dbo.tblClient.City,

T2DCBDATA.dbo.tblState.Description as State,

T2DCBDATA.dbo.tblClient.PostCode,

T2DCBDATA.dbo.tblClient.Telephone,

T2DCBDATA.dbo.tblClient.Fax,

T2DCBDATA.dbo.tblClient.email,

T2DCBDATA.dbo.tblClient.URL

from T2DCBDATA.dbo.tblClientExtraDetails

inner join tblClient on T2DCBDATA.dbo.tblClient.ClientID = T2DCBDATA.dbo.tblClientExtraDetails.ClientID

inner join tblState on T2DCBDATA.dbo.tblClient.StateID = T2DCBDATA.dbo.tblState.StateID

Another observation trying with a different table, I can successfully add the 'flight_data' table as a dataset by clicking the add button next to the name of the table but if I try to add the table using the below query with the 'Create Dataset with SQL' option, the query will validate successfully but when I go to save it as a dataset I get the error message below the query.

select FlightExternal.dbo.flight_data.*

from FlightExternal.dbo.flight_data

Error message:

Could not create dataset: Java.io. Ioexception: unable to init multipart upload to s3://d-us-w1-p00027-us-west-2/futurama-production/384/futurama-production/384/1172/queryresults/ricardo.hernandez%40cbh.com/.trifacta/ingest-job-staging-a91e62a0-4a94-4768-bad0-8f0b77bc3d56/ingested_data.json.

[Trifacta_Alumni]

Hi @Ricardo Hernandez?,

Thanks for reaching out about this. I have some questions, for context.

What is the version of SQL Server?

Does the first query test as valid (i.e., click the Validate SQL button in the UI)? If so, here are some other ideas to try:

• Add a semi-colon and new-line at the end of the query
• Fully qualify the table names in the INNER JOINs (e.g., inner join T2DCBDATA.dbo.tblClient on ...)

Let me know how it goes.

For the second query, I searched for the error in our system and there is an investigation underway for similar. Will let you know what I find. In the meantime, what happens if you re-phrase the query as follows: select * from FlightExternal.dbo.flight_data;

Let me know how it goes.

Cheers,

Nathanael

[Trifacta_Alumni]

@Nathanael Kuipers? thank you for assisting, please see below for answers in-line. 

• What is the version of SQL Server? SQL 2016 
• Does the first query test as valid (i.e., click the Validate SQL button in the UI)?
  • No, I receive, "Invalid SQL: Table not found - com.trifacta.datasystem.connect.exception.NoSuchTableException"
  • I receive the same after adding the semicolon(;) and new line

• If so, here are some other ideas to try:
  • Add a semi-colon and new-line at the end of the query
    • I receive, "Invalid SQL: Table not found - com.trifacta.datasystem.connect.exception.NoSuchTableException"
  • Fully qualify the table names in the INNER JOINs (e.g., inner join T2DCBDATA.dbo.tblClient on ...)
    • Are these not already fully qualified?
      • inner join tblClient on T2DCBDATA.dbo.tblClient.ClientID = T2DCBDATA.dbo.tblClientExtraDetails.ClientID
    • inner join tblState on T2DCBDATA.dbo.tblClient.StateID = T2DCBDATA.dbo.tblState.StateID;

 Regarding the second query, I used select * from FlightExternal.dbo.flight_data; on a single line and still received the below error.

Could not create dataset: Java.io. Ioexception: unable to init multipart upload to s3://d-us-w1-p00027-us-west-2/futurama-production/384/futurama-production/384/1172/queryresults/ricardo.hernandez%40cbh.com/.trifacta/ingest-job-staging-893dac85-8a89-45fd-afa1-defe1a624353/ingested_data.json. Download error report. 

json error report:

[

{

"jobGroupId": 17702,

"workspaceId": 384,

"status": "Failed",

"jobId": 49892,

"personId": 1172,

"errorMessageId": 2066,

"errorTimestamp": "2023-03-27T13:32:29.000Z",

"uiErrorMessage": {

"title": "TrifactaException"

}

}

]

[Trifacta_Alumni · Accepted solution]

Hi @Ricardo Hernandez?,

OK, I found an SQL Server instance to test with. The following is mostly concerned with the first, table-not-found error.

It is very easy to reproduce this error, albeit in a contrived way. Consider the following:

• Database: tempdb
• Schema: guest
• Table: 2010_ticket_20

This SQL query will unsurprisingly generate the table-not-found error when I click Validate SQL: SELECT * FROM tempdb.guest;

But this SQL query -- which includes a table name -- generates a different error: SELECT * FROM tempdb.guest.2010_ticket_20;

The error from the second SQL query is...

"Invalid SQL: A system error occurred: Incorrect syntax near '.2010'. - com.trifacta.datasystem.connect.exception.UnknownJdbcException"

...and can be corrected by updating the query as follows: SELECT * FROM "tempdb"."guest"."2010_ticket_20";

Notice the double quotes around the identifiers. Similarly, for SELECTing a single column I can use either of the following:

• SELECT customer_id FROM "tempdb"."guest"."2010_ticket_20";
• SELECT "tempdb"."guest"."2010_ticket_20".customer_id FROM "tempdb"."guest"."2010_ticket_20";

From this we can conclude that...

• As we can force the client to throw the table-not-found error by purposely feeding it incomplete information, we must assume the error is legitimate
• Syntax may be playing a role; we can delineate database.schema.table with double-quotes (Note: square brackets also work, e.g., [database].[schema].[table], which I find more readable)

Armed with our new knowledge, let's consider the following query which is similar to yours:

SELECT [tempdb].[guest].[2010_ticket_20].ticket_price, [tempdb].[guest].[datetime_test].transaction_date FROM [tempdb].[guest].[2010_ticket_20] INNER JOIN datetime_test ON [tempdb].[guest].[2010_ticket_20].customer_id = [tempdb].[guest].[datetime_test].customer_id;

This results in the table-not-found error! Here is the solution.

SELECT [tempdb].[guest].[2010_ticket_20].ticket_price, [tempdb].[guest].[datetime_test].transaction_date FROM [tempdb].[guest].[2010_ticket_20] INNER JOIN [tempdb].[guest].[datetime_test] ON [tempdb].[guest].[2010_ticket_20].customer_id = [tempdb].[guest].[datetime_test].customer_id;

And this can actually be cleaned up...

SELECT tempdb.guest.[2010_ticket_20].ticket_price, tempdb.guest.datetime_test.transaction_date FROM tempdb.guest.[2010_ticket_20] INNER JOIN tempdb.guest.datetime_test ON tempdb.guest.[2010_ticket_20].customer_id = tempdb.guest.datetime_test.customer_id;

...because it turns out that delineating stuff doesn't seem to matter for names that begin with an alpha character.

In summary:

• Fully qualify all table names, everywhere in the query
• Delineating database.schema.table may also be necessary in some cases

Please re-visit both queries accordingly. If the second query continues to fail, then please open a support ticket by emailing support@trifacta.com and include a link to this thread, for reference.

I hope this helps!

Cheers,

Nathanael

[Trifacta_Alumni]

@Nathanael Kuipers? 

Thanks again! With your suggestions I was able to resolve all the issues I had.