How do I remove the “.000000” part of the “2386.000000” field? I want to leave only the numerical part before the dot in databricks Answer You can use cast
Tag: databricks
How to change data type in csv column
Using databricks with SQL, I have to import my csv dataset into a table and analyse data using it. My problem is after I imported csv dataset, all column are String type, but some of these need to be Numeric. How can I solve? How can I define the column types of a csv file? I tried converting file in
Group by range of dates from date_start to date_end columns
I have a table with following table structure: I want to count how many events (each row is an event) was in every place by each month. If event dates refer to several months, it should be counted for all affected months. place_id could be repeated, so I did the following query: So I get following grouped tab…
Select rows from a table which contains at-least one alphabet in the column
I have column called name under a table in Databricks. I want to find a way to select only those rows from a table, which contains at-least one alphabet character in the name column. Example values in the column: Expected: I need to pick only those values which contains at least one alphabet in it. Or in othe…
Databricks error when change type column from string to numeric
I have a error like: com.databricks.backend.common.rpc.DatabricksExceptions$SQLExecutionException: org.apache.spark.sql.catalyst.parser.ParseException: no viable alternative at input ‘alter table x9concsheet5 Add netto_resultaat'(line 1, pos 29)* when i run this sql command in databricks: %sql alter tab…
Set a databricks python variable with a %sql statement
The code in cell 1 works just fine. The problem I just don’t know the correct keywords to search to see how I can make the code in cells 2 & 3 work. Basically, in a %sql cell, can I select into a variable that can be later used in a python cell? I realize this may not even be
Filter dictionary in pyspark with key names
Given a dictionary like column in a dataset, I want to grab the value from a key given that the value from another key is satisfied. Example: Say I have a column ‘statistics’ in a dataset, where each data row looks as: I want to get the value of ‘eye’ whenever hair is ‘black̵…
How to deal with ambiguous column reference in sql column name reference?
I have some code: I then try but I get an error Error in SQL statement: AnalysisException: Reference ‘A.CDE_WR’ is ambiguous, could be: A.CDE_WR, A.CDE_WR.; line 6 pos 4 in databricks. How can I deal with this? Answer This query: is using SELECT *. The * is shorthand for all columns from both tabl…
JDBC connection from Databricks to SQL server
I have a scenario where I need to trigger Stored procedure in the SQL server from Databricks. With the spark SQL connector,I can able to SELECT but not trigger the stored procedure. I am trying to …
SQL How to flag a given sessionID based on values from another column
Forgive me I’m not massively familiar with SQL to do the below. But would love to learn the process of how to do it if possible. I only have one table: Table name – SessionTracker I’d like to From the example above: I want to check is if a session which consists of request, event & respo…