Importing the passenger data
Use Hue to import data which is not present in your data warehouse, but is available on your computer or cloud storage such as Amazon S3.
In this exercise, the passenger data is not already available in Hue or in CDP but instead is available on your computer in a CSV file. Import the passenger data into Hue from your computer as follows:
Go to Importer and click .. at
the end of the Path field.
Choose a file pop-up is displayed.
Type s3a:// in the address text box and press
The S3 buckets associated with the CDW environment are displayed. You can narrow down the list of results using the search option.
- Navigate to the bucket in which you want to upload the file and click Upload a file.
Select the CSV file that you want to import into Hue.
Hue displays the preview of the table along with the format.Hue automatically detects the field separator, record separator, and the quote character from the CSV file. If you want to override a specific setting, select a different value from the drop-down list.
On this page under DESTINATION, enter airline_ontime_orc.unique_tickets in the Name field.The
unique_ticketstable will be created in the
- Select Format as Text.
Expand Extras and select the Store in Default
Change all the “
bigint” column types to “
int” under FIELDS and click Submit.The CREATE TABLE query is triggered.Hue displays the logs and opens the Table Browser from which you can view the newly created table when the operation completes successfully.
Run the following query to find passengers with international connecting
-- Query to explore passenger manifest data: do we have international connecting flights? SELECT * FROM `airline_ontime_orc`.unique_tickets a, `airline_ontime_orc`.flights o, `airline_ontime_orc`.flights d, `airline_ontime_orc`.airports oa, `airline_ontime_orc`.airports da WHERE a.leg1flightnum = o.flightnum AND a.leg1uniquecarrier = o.uniquecarrier AND a.leg1origin = o.origin AND a.leg1dest = o.dest AND a.leg1month = o.month AND a.leg1dayofmonth = o.dayofmonth AND a.leg1dayofweek = o.`dayofweek` AND a.leg2flightnum = d.flightnum AND a.leg2uniquecarrier = d.uniquecarrier AND a.leg2origin = d.origin AND a.leg2dest = d.dest AND a.leg2month = d.month AND a.leg2dayofmonth = d.dayofmonth AND a.leg2dayofweek = d.`dayofweek` AND d.origin = oa.iata AND d.dest = da.iata AND oa.country <> da.country ;