- 6023 Views
- 3 replies
- 2 kudos
SQL While do loops
Hello. Could you please suggest a workaround for a while do loop in Databricks SQL?WHILE LSTART>0 DO SET LSTRING=CONCAT(LSTRING, VSTRING2)Thank you in advance
- 6023 Views
- 3 replies
- 2 kudos
- 2 kudos
@elgeo Here are two alternatives.1. Use a recursive CTEWITH RECURSIVE loop_cte (lstart, lstring) AS ( SELECT 5 AS lstart, '' AS lstring UNION ALL SELECT lstart - 1, CONCAT(lstring, 'VSTRING2') FROM loop_cte WHERE lstart > 1 ) SELECT * FROM ...
- 2 kudos
- 39410 Views
- 13 replies
- 6 kudos
SQL Stored Procedure in Databricks
Hello. Is there an equivalent of SQL stored procedure in Databricks? Please note that I need a procedure that allows DML statements and not only Select statement as a function provides.Thank you in advance
- 39410 Views
- 13 replies
- 6 kudos
- 6 kudos
how to connect the stored procedures to power bi report builder, seems like it is not working
- 6 kudos
- 15066 Views
- 4 replies
- 4 kudos
Resolved! UUID/GUID Datatype in Databricks SQL
Hi all,I am trying to create a table with a GUID column.I have tried using GUID, UUID; but both of them are not working.Can someone help me with the syntax for adding a GUID column?Thanks!
- 15066 Views
- 4 replies
- 4 kudos
- 4 kudos
> What ANSI SQL data structure to use for UUID or GUID?I had similar question. The answer was `STRING`.
- 4 kudos
- 2568 Views
- 4 replies
- 2 kudos
How to search an array of words in a text field
Example:TABLE 1FIELD_TEXTI like salty food and Italian foodI have Italian foodbread, rice and beansmexican foodscoke, spritearray['italia', 'mex','coke']match TABLE1 X ARRAYResults:I like salty food and Italian foodI have Italian foodmexican foodsis ...
- 2568 Views
- 4 replies
- 2 kudos
- 2 kudos
Yes, it’s possible to search an array of words in a text field using SQL with LIKE clauses or regex functions, while PySpark provides higher scalability with functions like rlike and array_contains (Wikipedia explains that SQL is a domain-specific la...
- 2 kudos
- 20183 Views
- 14 replies
- 10 kudos
How to overcome missing query parameters in Databricks SQL?
Hi, there! I'm trying to build up my first dashboard based on Dataabricks SQL. As far as I can see if you define a query parameter you can't skip it further. I'm looking for any option where I can make my parameter optional. For instance, I have a ta...
- 20183 Views
- 14 replies
- 10 kudos
- 10 kudos
Hello guys, I'm not sure if you could solve this issue but here is how I've handled it:SELECT *FROM my_tableWHERE (CASE WHEN LEN(:my_parameter) > 0 THEN my_column = :my_parameter ELSE my_column = my_column END)I hope this can help!
- 10 kudos
- 24360 Views
- 16 replies
- 5 kudos
Resolved! Databricks SQL - Unable to Escape Dollar Sign ($) in Column Name
It seems that due to how Databricks processes SQL cells, it's impossible to escape the $ when it comes to a column name.I would expect the following to work:%sql SELECT 'hi' `$id`The backticks ought to escape everything. And indeed that's exactly wha...
- 24360 Views
- 16 replies
- 5 kudos
- 5 kudos
I needed a $ character in the context of Java format strings in the SQL printf function.With some trial and error, I found workarounds:A trailing backslash escapes the dollar character successfully, like soSELECT printf('%1$\s, %1$\s', 'hi') AS testC...
- 5 kudos
- 14435 Views
- 10 replies
- 1 kudos
Resolved! Performance enhancement while writing dataframes into Parquet tables
Hi,I am trying to write the contents of a dataframe into a parquet table using the command below.df.write.mode("overwrite").format("parquet").saveAsTable("sample_parquet_table")The dataframe contains an extract from one of our source systems, which h...
- 14435 Views
- 10 replies
- 1 kudos
- 1 kudos
I am Bob Clarke marketing manager of virtual assistants Pakistan and I help companies hire amazon virtual assistants who manage product listings order processing and inventory updates. Our trained staff improves efficiency and boosts sales. We suppor...
- 1 kudos
- 25668 Views
- 3 replies
- 0 kudos
spark is case sensitive? Spark is not case sensitive by default. If you have same column name in different case (Name, name), if you try to select eit...
spark is case sensitive?Spark is not case sensitive by default. If you have same column name in different case (Name, name), if you try to select either "Name" or "name" column you will get column ambiguity error.There is a way to handle this issue b...
- 25668 Views
- 3 replies
- 0 kudos
- 0 kudos
Hi I had similar issues with parquet files when trying to query athena, fix was i had to inspect the parquet file since it contained columns such as "Name", "name" which the aws crawler / athena would interpret as a duplicate column since it would se...
- 0 kudos
- 4306 Views
- 4 replies
- 2 kudos
Error in SQL Warehouse: User is not part of org
I tried to start the Databricks SQL Warehouse cluster today, but received the following error message:Clusters are failing to launch. Cluster launch will be retired Request to create a cluster failed with an exception: PERMISSION_DENIED: User xxxx is...
- 4306 Views
- 4 replies
- 2 kudos
- 2 kudos
Hi All,What you need to do is set a new owner. You can do this by clicking on permissions, then the setup icon, and choosing Assign new owner.It works for me.Thanks
- 2 kudos
- 7997 Views
- 6 replies
- 0 kudos
Error in SQL statement: UnsupportedOperationException: Cannot perform Merge as multiple source rows matched and attempted to modify the same
My code:- CREATE OR REPLACE TEMPORARY VIEW preprocessed_source ASSELECT Key_ID, Distributor_ID, Customer_ID, Customer_Name, ChannelFROM integr_masterdata.Customer_Master;-- Step 2: Perform the merge operation using the preprocessed source tableM...
- 7997 Views
- 6 replies
- 0 kudos
- 0 kudos
This error occurs; when we try to update all the cells of target_data without a single updated record in source_data(updates_data) , to resolve this issue add a update_time column with unix timestamp (or) make changes in at least one cell of streamin...
- 0 kudos
- 12647 Views
- 9 replies
- 5 kudos
Databricks JDBC & Remote Write
Hello,I'm trying to write to a Delta Table in my Databricks instance from a remote Spark session on a different cluster with the Simba Spark driver. I can do reads, but when I attempt to do a write, I get the following error:{ df.write.format("jdbc...
- 12647 Views
- 9 replies
- 5 kudos
- 6767 Views
- 3 replies
- 7 kudos
what formatter is used to format SQL cell in databricks
Databricks launched formatter Black which formats python cells, I can also see SQL cell getting formatted, but not sure which formatter is being used for SQL cell formatting. No clarity given on docs.
- 6767 Views
- 3 replies
- 7 kudos
- 7 kudos
The formatter is mentioned here: Develop code in Databricks notebooks - Azure Databricks | Microsoft LearnIt is this npm package: @gethue/sql-formatter - npm
- 7 kudos
- 54753 Views
- 5 replies
- 3 kudos
Resolved! How to set a variable and use it in a SQL query
I want to define a variable and use it in a query, like below: %sql SET database_name = "marketing"; SHOW TABLES in '${database_name}';However, I get the following error:ParseException: [PARSE_SYNTAX_ERROR] Syntax error at or near ''''(line 1, pos...
- 54753 Views
- 5 replies
- 3 kudos
- 3 kudos
CJS had the best answer by virtue of it being code-based rather than widget-based. In a notebook where the value of the variable must continually be reset, widgets are suboptimal. That said, the easiest way is to wrap the code in python:%pyvar1 = s...
- 3 kudos
- 9781 Views
- 6 replies
- 5 kudos
Resolved! exception when using java SQL client
Hi,I try to use java sql. i can see that the query on databricks is executed properly.However, on my client i get exception (see below).versions:jdk: jdk-20.0.1 (tryed also with version 16, same results)https://www.oracle.com/il-en/java/technologies/...
- 9781 Views
- 6 replies
- 5 kudos
- 28690 Views
- 19 replies
- 15 kudos
How to enable/verify cloud fetch from PowerBI
I tried to benchmark the Powerbi Databricks connector vs the powerbi Delta Lake reader on a dataset of 2.15million rows. I found that the delta lake reader used 20 seconds, while importing through the SQL compute endpoint took ~75 seconds. When I loo...
- 28690 Views
- 19 replies
- 15 kudos
- 15 kudos
I'm troubleshooting slow speeds (~6Mbps) from Azure Databricks to the PowerBI Service (Fabric) via dataflows.Drivers are up to date. PowerBI is using Microsoft's Spark ODBC driver Version 2.7.6.1014, confirmed via log4j.HybridCloudStoreResultHandler...
- 15 kudos