Cloudera Docs

SQL queries

In Cloudera Data Visualization, SQL query visuals enable you to process SQL language queries using an enhanced visual editor for SQL authoring, including autocomplete.

  1. Start a new visual based on the US County Population dataset.
    For instructions, see Creating a visual.
  2. Select SQL Queries in the VISUALS menu.

    The shelves of the visual changed. You can find a link in the shelf area that leads you to the visual in full-screen mode, where you can view/edit the SQL statement in the SQL editor.

  3. Enter the following text in the Enter/Edit SQL Expression modal window: 
    SELECT 
    TA_0.`stname` as `State`, 
    TA_0.`ctyname` as `County`, 
    (sum((TA_0.`wa_male`))+sum((TA_0.`wa_female`))) as `White`, 
    (sum((TA_0.`ba_male`))+sum((TA_0.`ba_female`))) as `Black or African American`, 
    (sum((TA_0.`ia_male`))+sum((TA_0.`ia_female`))) as `American Indian and Alaska Native`, 
    (sum((TA_0.`aa_male`))+sum((TA_0.`aa_female`))) as `Asian`, 
    (sum((TA_0.`na_male`))+sum((TA_0.`na_female`))) as `Native Hawaiian and Other Pacific Islander`, 
    (sum((TA_0.`tom_male`))+sum((TA_0.`tom_female`))) as `Two or More Races`, 
    sum((TA_0.`tot_pop`)) as `Total Population`
  FROM `samples`.`us_counties` TA_0
  GROUP BY 1, 2
  LIMIT 5000
  4. Click VALIDATE & REFRESH.

    The visual is updated in the preview area. You can make further edits to the SQL code and check the changes by validating and refreshing the expression again.

    If you do not need the autocomplete function, uncheck the Autocomplete on option.

  5. Click APPLY to save the changes.
    If you do not want to apply your changes, click CANCEL or just close the modal window.
  6. Change the title to County Populations, by State /County - Query.

    [Optional] You can also add a brief description of the visual below the title of the visualization.

  7. Click SAVE at the top left corner of the Dashboard Designer.

The table created from the query should look similar to this:

As an example of using this visual, sort on the column Native Hawaiian and Pacific Islander to see where most people of that ethnicity live. You can notice that there is a very large population in Los Angeles, San Diego, and in the Salt Lake area.