ParseException: extraneous input 'STRING' expecting {')', ',', 'CONSTRAINT'}

Question

while creating a delta table im getting parse exception error: ParseException: extraneous input 'STRING' expecting {')', ',', 'CONSTRAINT'}

csv file includes the column names and datatype

#defining a function to detect if any spanish characters present in column names

def isEnglish(s):
    try:
        s.encode(encoding='utf-8').decode('ascii')
    except UnicodeDecodeError:
        dataC.append("`"+i.replace(" ", "_").replace("(", "").replace(")", "").replace("/", "")+"`")
    else:
        dataC.append(i)

def isSpecialCharacters(s):
  special_characters = ['!','@','#','$','%','^','&','*','(',')','-','+','?','=','<','>','/']
  a_match = [True for match in special_characters if match in s]
  if True in a_match:
    dataD.append("`"+j.replace(" ", "_").replace("(", "").replace(")", "").replace("/", "")+"`")
  else:
    dataD.append(j)


import csv

# Open the file in 'r' mode, not 'rb'
csv_file = open('/dbfs/mnt/reservoir/Mapping_List.csv','r',encoding='latin1')
dataA = []
dataB = []
dataC = []
dataD = []
# Read off and discard first line, to skip headers
csv_file.readline()
# Split columns while reading
for a, b, c in csv.reader(csv_file, delimiter=','):
    # Append each variable to a separate list
    dataA.append(a)
    dataB.append(b)
#checking whether any spanish characters present in list
for i in dataA:
    isEnglish(i)
for j in dataC:
    isSpecialCharacters(j)
file_list=list(zip(dataD,dataB))
final_list=[]
for i,j in file_list:
    res=i+' '+j.upper()
    final_list.append(res)
result=','.join(final_list)
#print(result)
query="CREATE TABLE IF NOT EXISTS  Mapping_List ({}) USING DELTA LOCATION '/mnt/reservoir/Mapping_List'".format(result)

sqlContext.sql(query)

while i create the table as below format its working fine:

%sql

CREATE TABLE IF NOT EXISTS Mapping_List (
  code STRING,
  Name STRING,
  Group STRING,
  Year STRING,
  Trasm STRING,
  ID STRING,
  3_AVC_VFA STRING,
  3_AVC_TRA STRING,
  Code_1 STRING,
  Code_2 STRING,
  Code_3 STRING,
  Code_4 STRING,
  Code_5 STRING,
  Code_6 STRING,
  Code_7 STRING,
  Code_8 STRING,
  Code_9 STRING,
  Code_10 STRING
  )
USING DELTA
LOCATION '/mnt/reservoir/Mapping_List'

why am I getting the parse exception in first query?

Traceback error:

databricks/spark/python/pyspark/sql/context.py in sql(self, sqlQuery)
    452         [Row(f1=1, f2='row1'), Row(f1=2, f2='row2'), Row(f1=3, f2='row3')]
    453         """
--> 454         return self.sparkSession.sql(sqlQuery)
    455 
    456     def table(self, tableName):

/databricks/spark/python/pyspark/sql/session.py in sql(self, sqlQuery)
    775         [Row(f1=1, f2='row1'), Row(f1=2, f2='row2'), Row(f1=3, f2='row3')]
    776         """
--> 777         return DataFrame(self._jsparkSession.sql(sqlQuery), self._wrapped)
    778 
    779     def table(self, tableName):

/databricks/spark/python/lib/py4j-0.10.9.1-src.zip/py4j/java_gateway.py in __call__(self, *args)
   1302 
   1303         answer = self.gateway_client.send_command(command)
-> 1304         return_value = get_return_value(
   1305             answer, self.gateway_client, self.target_id, self.name)
   1306 

/databricks/spark/python/pyspark/sql/utils.py in deco(*a, **kw)
    121                 # Hide where the exception came from that shows a non-Pythonic
    122                 # JVM exception message.
--> 123                 raise converted from None
    124             else:
    125                 raise

ParseException: 
extraneous input 'STRING' expecting {')', ',', 'CONSTRAINT'}

Saideep Arikontham · Answer

The error ParseException: extraneous input 'STRING' expecting {')', ',', 'CONSTRAINT'} has occurred because the query that has been built has incorrect syntax.

I have used the following queries for demonstration and got the same error:

sqlContext.sql("CREATE TABLE IF NOT EXISTS  demo3( id string, gname String string)")

enter image description here

sqlContext.sql("CREATE TABLE IF NOT EXISTS  demo3( id id String, gname String)")

enter image description here

There might be other possibilities as to why this error occurs. So, use print(query) to check what is the query that you are executing. Use the traceback error to identify the position where the error is occurring and correct it to overcome the error.

ParseException: extraneous input 'STRING' expecting {')', ',', 'CONSTRAINT'}

1 Answers