Cloudera Docs

Creating a join

Learn how you can create new data joins in a dataset in CDP Data Visualization.

This example shows you how to create new data joins using the Flight Delays dataset.

  1. On the main navigation bar, click DATA.

    The Data view appears, open on the Datasets tab.

  2. Create a new dataset based on the sample datafile.
  3. Find the dataset in the list of datasets, either by scrolling or by using search, and click it.
    Dataset side navigation appears, open at Dataset Detail view.
  4. In the side navigation menu, click Data Model.
    The Data Model view appears, and shows the name of the only table in the dataset. You may click SHOW DATA to display the data of that table.
  5. Click EDIT DATA MODEL.
  6. Click the plus sign on the table representation.

    The Table Browser modal window appears.

  7. In the Table Browser modal window, make the following selections:

    • In the Database Name selector, choose the database documentation.

    • In the Table Name selector, choose the table name airline_id.

      This value is pre-populated to match the existing table of the dataset, but it may be changed.

    • Click SELECT.

    The Edit Join modal window appears.

  8. In the Edit Join modal window, the following options are available:
    1. [Optional] Click Clear Fields to clear all already defined joins between the two tables.
    2. [Optional] Click sample data to preview the data. Click again to hide sample data.
    3. [Optional] Click Add Join Pair to add another column connection between the same two tables.
    4. [Optional] Click Add Join Expression to add a join between the two tables based on a custom SQL expression.
    5. [Optional] Click icon (minus) to remove an existing join pair or an existing join expression.
    6. [Optional] Under Join Expressions, click the text box to open the Join Expression interface and specify or update a custom SQL expression that defines the join conditions.
    7. Click APPLY to save the changes.
  9. In the Edit Join modal window, do the following:
    • Select the matching columns for both tables. On the left side, select the field airlineid. On the right side, select the field code.
    • Click Sample Data to view some data in both columns, and verify that the join makes sense. Click again to hide sample data.
  10. Click APPLY.
  11. Repeat the previous two steps to create seven more joins as follows:

    • The table airport_codes has two joins to the main table, and you must create each join separately as follows:

      • Left column origin = right column code.
      • Left column dest = right column code.
    • The table cancellation_code has a join for left column cancellationcode = right column code.
    • The table airport_lat_long has two joins to the main table, and you must create each join separately as follows:
      • Left column origin = right column locationid.
      • Left column dest = right column locationid.
    • The table state_abbreviations has two joins to the main table, and you must create each join separately:
      • Left column deststate = right column abbreviation.
      • Left column originstate = right column abbreviation.

      This step is optional, and depends on whether your flights_* table has fully extended state names.

  12. Click SAVE.
  13. Optional: Click the (link) icon to edit joins or to change join type.