Write in Database Query Truncation

I have a very large job where everything is in database (Databricks). At the end of the job, I am attempting to write to a table but it fails every time with a syntax/semantics error. I have used a dynamic output tool right before the write in database tool to output the query. The query is roughly 40,000 lines, it runs fine when I run it in Databricks directly. The issue seems to be the write in database tool itself, I think it might be truncating the query causing the syntax error. Is this possible? If so, what are alternative solutions?

In Database

In-Database

Accepted answers

All comments

apathetichell

It's possible but more likely:

1) are you using write in-db? I have NEVER gotten write-in-db to work with Databricks --- only Datastream-in...

2) can you try to write a smaller amount?

3) Cluster timeout ---> this happens. a lot. extend your cluster timeout limit for your databricks account/token.

4) Databricks SQL issues ---> there could be something in Alteryx that Databricks sql can't handle or vice versa. Turn on logging in your ODBC driver --- not Alteryx. Examine your ODBC driver logs.

thomas598henry

@caseyz wrote:
I have a very large job where everything is in database (Databricks). At the end of the job, I am attempting to write to a table but it fails every time with a syntax/semantics error. I have used a dynamic output tool right before the write in database tool to output the query. The query is roughly 40,000 lines, it runs fine when I run it in Databricks directly. The issue seems to be the write in database tool itself, I think it might be truncating the query causing the syntax error. Is this possible? If so, what are alternative solutions?

Hello,

Yes, the write-in-database tool may be truncating your large query, leading to syntax errors. This is a known issue with some tools that have query size limits. As an alternative, consider:

- Writing the query directly in Databricks using notebooks or SQL endpoints
- Breaking the query into smaller chunks or using stored procedures
- Using external tools like Snaplogic or custom Python scripts with JDBC for better control

These approaches help bypass tool limitations and ensure full query execution.

Best Regards,
Thomas Henry

dataguyW

If your job is that big, is there any way in Databricks to store it as a stored procedure or something that you can just call? 40K lines is a lot of code. I agree with the other suggestions of can you break it up.

simonaubert_bd

40 000 lines of SQL ? Can you please explain why it is so huge?

apathetichell

@simonaubert_bd --- thats from output in-db of the query which Alteryx is using. If you throw a bunch of In-DB tools-> and have a slew of fields in each tool ---> you can get there pretty fast.

simonaubert_bd

@apathetichellThe (I hope good) practice I have on in-db to avoid that is to use temporary tables. It costs a little storage but it can help for performance (or not) and make the queries simpler.

edit : according to the OP, he uses a dynamic input tool, meaning he somehow made the code that huge, not the in-db nesting logic .

dataguyW

I'm sure there is a reason why, but I can say that can't be fun to debug 😀 Temp table approach to break things into pieces took a co-workers job from nearly 2 hours down to about 25 minutes. I think that is a good path to attempt for sure @caseyz

apathetichell

@caseyz --- did you get this to work?

@dataguyW @simonaubert_bd -- I do not believe Databricks supports temp tables. It has temporary views --- but I don't think Alteryx supports them (not sure about the underlying driver).

Back to my original points ---> 1) use datastream in vs write in-db.

apathetichell

@caseyz where are we at here? did this work?

would love to understand if write-in-db is even working for databricks right now... my memory is databricks must use datastream-in.--- and this makes it look like it still requires datastream-in.

https://help.alteryx.com/current/en/designer/data-sources/databricks.html#idp411866

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703