Zeppelin notebook中表达式'alias'里'*'使用无效问题求助

Hey there! Let's figure out what's causing that Invalid usage of '*' in expression 'alias' error in your Zeppelin notebook.

First, let's break down the error: Spark throws this when it encounters a * in a part of the SQL query where it doesn't belong—most commonly when you try to use * in a GROUP BY clause, or if you accidentally include * as a column alias (which isn't allowed).

Looking at your code, here are the likely issues and fixes:

1. Missing table selection (critical!) #

Your code references a table variable, but there's no code to let users select a table via Zeppelin's dynamic forms. Without this, table might be undefined or set to an invalid value (like *), which breaks your SQL queries.

Add this line at the top to let users pick a table first:

val table = z.select("Select a table", tables())

2. SQL Injection Risk & Column/Table Name Safety #

When concatenating column and table names into SQL, you should wrap them in backticks (`) to handle names with spaces, special characters, or reserved words. This also prevents accidental syntax errors.

Update your query variable to use backticks and cleaner string interpolation:

val query = s"""
  SELECT `$col1`, `$col2`, COUNT(*) AS count 
  FROM `$table` 
  GROUP BY `$col1`, `$col2` 
  ORDER BY count DESC
"""

3. Fixing the Table Output in Zeppelin #

Your current code uses println("%table ..."), which won't work correctly—Zeppelin's %table magic needs to be the first thing in the output, not wrapped in a println. Instead, print the header and rows directly after the magic:

// Print the %table magic first, then the header and rows
println("%table")
println(df.columns.mkString("\t"))
println(df.map(_.mkString("\t")).collect().mkString("\n"))

Alternatively, you can use Zeppelin's built-in z.show(df) to render the DataFrame as an interactive table without manual printing—it's simpler and more reliable!

4. Ensuring z.select Returns Valid Columns #

Your columns function correctly fetches actual column names from the table (no *), but adding a default column value to z.select prevents empty selections that could break the SQL:

// Replace "default_column1" and "default_column2" with valid columns from your table
val col1 = z.select("Select first column", columns(table), "default_column1")
val col2 = z.select("Select second column", columns(table), "default_column2")

Full Fixed Code #

Here's the complete revised code incorporating all these fixes:

def list(table: String, col: String): Array[(String, String)] = {
  sqlContext.sql(s"SELECT DISTINCT `$col` FROM `$table` ORDER BY `$col`")
    .collect.map(x => (x(0).asInstanceOf[String], x(0).asInstanceOf[String]))
}

def tables(): Array[(String, String)] = {
  sqlContext.sql("SHOW TABLES")
    .collect.map(x => (x(0).asInstanceOf[String], x(0).asInstanceOf[String]))
}

def columns(table: String): Array[(String, String)] = {
  sqlContext.sql(s"SELECT * FROM `$table` LIMIT 0")
    .columns.map(x => (x, x))
}

// Add table selection dropdown
val table = z.select("Select a table", tables())

// Add column selections with defaults (replace with your actual column names)
val col1 = z.select("Select first column", columns(table), "default_column1")
val col2 = z.select("Select second column", columns(table), "default_column2")

// Safely build the SQL query with backticks and interpolation
val query = s"""
  SELECT `$col1`, `$col2`, COUNT(*) AS count 
  FROM `$table` 
  GROUP BY `$col1`, `$col2` 
  ORDER BY count DESC
"""

val df = sqlContext.sql(query)

// Correctly render the table in Zeppelin (simpler built-in method)
z.show(df)

Why This Works #

• The missing table dropdown ensures you're always querying a valid table.
• Backticks around table/column names prevent syntax errors from special characters or reserved words.
• String interpolation makes the SQL easier to read and less error-prone than concatenation.
• Default values for column selections prevent empty values from breaking the SQL.
• z.show(df) handles table rendering correctly without manual string formatting.

Give this a try, and let me know if you run into any other issues!

内容的提问来源于stack exchange，提问作者john