I am trying to create a sql script that counts the number of unique values for each column in a table. I do not want to hardcode any column names. The below sql statement works in a SQL server management studio, but not in my instance of Hue Impala. I’m pretty new to Impala as well so I’m not quite sure why the below sql isn’t working. Running the sql below returns the following error: “ParseException: Syntax error in line 6: undefined: from INFORMATION_SCHEMA.COLUMNS ^ Encountered: COLUMNS Expected: DEFAULT, IDENTIFIER CAUSED BY: Exception: Syntax error”
declare @SQL nvarchar(max)
set @SQL = ''
;with cols as (
select Table_Schema, Table_Name, Column_Name, Row_Number() over(partition by
Table_Schema, Table_Name
order by ORDINAL_POSITION) as RowNum
from INFORMATION_SCHEMA.COLUMNS
)
select @SQL = @SQL + case when RowNum = 1 then '' else ' union all ' end
+ ' select ''' + Column_Name + ''' as Column_Name, count(distinct ' + quotename (Column_Name) + ' )
As DistinctCountValue,
count( '+ quotename (Column_Name) + ') as CountValue FROM ' + quotename (Table_Schema) + '.' +
quotename (Table_Name)
from cols
where Table_Name = 'my_table_name' --print
execute (@SQL)