How to fix the SQL query in databricks if column name has bracket in it

Question

I have a file which has data like this , I have converted that file into a databricks table.

Select * from myTable

Output:

Product[key] Product[name]
123          Mobile
345          television
456          laptop

I want to query my table for laptop data.

I am using below query

Select * from myTable where Product[name]='laptop'

I am getting below error in databricks:

AnalysisException: cannot resolve 'Product' given input columns: [spark_catalog.my_db.myTable.Product[key],[spark_catalog.my_db.myTable.Product[name]

Answer 1

When certain characters appear in column names of a table in SQL, you get a parse exception. These characters include brackets, dots (.), hyphens (-), etc. So, when such characters appear in column names, we need an escape character to parse these characters just as a part of column name.

For SQL in Databricks, this character is Backtick (`). Enclosing your column name in backticks ensures that your column name is parsed correctly as it is even when it includes characters like ‘[]’ (In this case).

Since you have converted a file data into Databricks table, you were not able to see the main problem which is parsing the column name. If you manually create a table with specified schema in Databricks, you will get the following result:

Once you use Backtick in the following way, using the column name would not be a problem anymore.

 create table mytable(`Product[key]` integer, `Product[name]` varchar(20)) 
 select * from mytable where `Product[name]`='laptop' 

Answer 2

If the jar is in the current directory,Change the shell to this.

cd target

echo "shell parameters: $*"

java -classpath .:./mysql-connector-java-8.0.27.jar com.tencent.Example $*