DBLINK in VerticaPy#
Introduction#
Starting with VerticaPy 0.12.0, you can work with other databases, such as PostgresQL and mySQL, using DBLINK functionality. DBLINK is a Vertica User Defined Transform Function coded in C++ that runs SQL against other databases. To setup and learn more about DBLINK in Vertica, please view the github repo.
In order to use this new functionality, we first need to install the ODBC driver and manager, as well as configure DBLINK on all nodes of the cluster. Configuration entails three files:
dblink.cids
odbc.ini
odbcinst.ini
For more information about requirements, see the github repo.
These files provide the host server address, username, and password, as well as the database name that we want to access. In future versions, we are planning to simplify this process and automate the creation of these files.
In the next section, let’s work through an example of a database in PostgreSQL.
Connecting to an External Database#
[1]:
# Importing VerticaPy
import verticapy as vp
We first need to provide the connection information that we have set up in the Connection Identifier Database file (dblink.cids). We can select a special character symbol to identify this connection.
Let’s try to set up a connection with an external PostgreSQL database, which we name “pgdb”. The connection details for “pgdb”, including server name, user name etc., are in the configuration files mentioned in the introduction section.
[2]:
# Setting up a connection with a database with the alias "pgdb"
vp.set_external_connection(cid = "pgdb",
rowset = 500,
symbol = "&")
Creating a vDataFrame#
We can create a vDataFrame from a table stored in an external database by setting the ‘external’ parameter to True. SQL can be used to fetch required data, and we can provide an identifying symbol that can be used for fetching perform queries with SQL.
[3]:
# Creating a vDataFrame using an SQL query by setting external = True.
tab_data = vp.vDataFrame(input_relation = "airports",
external = True,
symbol = "&")
tab_data
[3]:
Abc IATA_CODEVarchar(3) | Abc Varchar(100) | Abc CITYVarchar(60) | Abc STATEVarchar(2) | Abc COUNTRYVarchar(100) | 🌎 LATITUDENumeric(30) | 🌎 LONGITUDENumeric(30) | |
1 | ABE | Allentown | PA | USA | 40.65236 | -75.4404 | |
2 | ABI | Abilene | TX | USA | 32.41132 | -99.6819 | |
3 | ABQ | Albuquerque | NM | USA | 35.04022 | -106.60919 | |
4 | ABR | Aberdeen | SD | USA | 45.44906 | -98.42183 | |
5 | ABY | Albany | GA | USA | 31.53552 | -84.19447 | |
6 | ACK | Nantucket | MA | USA | 41.25305 | -70.06018 | |
7 | ACT | Waco | TX | USA | 31.61129 | -97.23052 | |
8 | ACV | Arcata/Eureka | CA | USA | 40.97812 | -124.10862 | |
9 | ACY | Atlantic City | NJ | USA | 39.45758 | -74.57717 | |
10 | ADK | Adak | AK | USA | 51.87796 | -176.64603 | |
11 | ADQ | Kodiak | AK | USA | 57.74997 | -152.49386 | |
12 | AEX | Alexandria | LA | USA | 31.32737 | -92.54856 | |
13 | AGS | Augusta | GA | USA | 33.36996 | -81.9645 | |
14 | AKN | King Salmon | AK | USA | 58.6768 | -156.64922 | |
15 | ALB | Albany | NY | USA | 42.74812 | -73.80298 | |
16 | ALO | Waterloo | IA | USA | 42.55708 | -92.40034 | |
17 | AMA | Amarillo | TX | USA | 35.21937 | -101.70593 | |
18 | ANC | Anchorage | AK | USA | 61.17432 | -149.99619 | |
19 | APN | Alpena | MI | USA | 45.07807 | -83.56029 | |
20 | ASE | Aspen | CO | USA | 39.22316 | -106.86885 | |
21 | ATL | Atlanta | GA | USA | 33.64044 | -84.42694 | |
22 | ATW | Appleton | WI | USA | 44.25741 | -88.51948 | |
23 | AUS | Austin | TX | USA | 30.19453 | -97.66987 | |
24 | AVL | Asheville | NC | USA | 35.43619 | -82.54181 | |
25 | AVP | Wilkes-Barre/Scranton | PA | USA | 41.33815 | -75.72427 | |
26 | AZO | Kalamazoo | MI | USA | 42.23488 | -85.55206 | |
27 | BDL | Windsor Locks | CT | USA | 41.93887 | -72.68323 | |
28 | BET | Bethel | AK | USA | 60.77978 | -161.838 | |
29 | BFL | Bakersfield | CA | USA | 35.4336 | -119.05677 | |
30 | BGM | Binghamton | NY | USA | 42.20848 | -75.97961 | |
31 | BGR | Bangor | ME | USA | 44.80744 | -68.82814 | |
32 | BHM | Birmingham | AL | USA | 33.56294 | -86.75355 | |
33 | BIL | Billings | MT | USA | 45.80766 | -108.54286 | |
34 | BIS | Bismarck | ND | USA | 46.77411 | -100.74672 | |
35 | BJI | Bemidji | MN | USA | 47.50942 | -94.93372 | |
36 | BLI | Bellingham | WA | USA | 48.79275 | -122.53753 | |
37 | BMI | Bloomington | IL | USA | 40.47799 | -88.91595 | |
38 | BNA | Nashville | TN | USA | 36.12448 | -86.67818 | |
39 | BOI | Boise | ID | USA | 43.56444 | -116.22278 | |
40 | BOS | Boston | MA | USA | 42.36435 | -71.00518 | |
41 | BPT | Beaumont/Port Arthur | TX | USA | 29.95083 | -94.02069 | |
42 | BQK | Brunswick | GA | USA | 31.25903 | -81.46631 | |
43 | BQN | Aguadilla | PR | USA | 18.49486 | -67.12944 | |
44 | BRD | Brainerd | MN | USA | 46.39786 | -94.13723 | |
45 | BRO | Brownsville | TX | USA | 25.90683 | -97.42586 | |
46 | BRW | Barrow | AK | USA | 71.28545 | -156.766 | |
47 | BTM | Butte | MT | USA | 45.9548 | -112.49746 | |
48 | BTR | Baton Rouge | LA | USA | 30.53316 | -91.14963 | |
49 | BTV | Burlington | VT | USA | 44.473 | -73.15031 | |
50 | BUF | Buffalo | NY | USA | 42.94052 | -78.73217 | |
51 | BUR | Burbank | CA | USA | 34.20062 | -118.3585 | |
52 | BWI | Baltimore | MD | USA | 39.1754 | -76.6682 | |
53 | BZN | Bozeman | MT | USA | 45.7769 | -111.15301 | |
54 | CAE | Columbia | SC | USA | 33.93884 | -81.11954 | |
55 | CAK | Akron | OH | USA | 40.91631 | -81.44247 | |
56 | CDC | Cedar City | UT | USA | 37.70097 | -113.09858 | |
57 | CDV | Cordova | AK | USA | 60.49183 | -145.47765 | |
58 | CEC | Crescent City | CA | USA | 41.78016 | -124.23653 | |
59 | CHA | Chattanooga | TN | USA | 35.03527 | -85.20379 | |
60 | CHO | Charlottesville | VA | USA | 38.13864 | -78.45286 | |
61 | CHS | Charleston | SC | USA | 32.89865 | -80.04051 | |
62 | CID | Cedar Rapids | IA | USA | 41.88459 | -91.71087 | |
63 | CIU | Sault Ste. Marie | MI | USA | 46.25075 | -84.47239 | |
64 | CLD | San Diego | CA | USA | 33.12723 | -117.27873 | |
65 | CLE | Cleveland | OH | USA | 41.41089 | -81.8494 | |
66 | CLL | College Station | TX | USA | 30.58859 | -96.36382 | |
67 | CLT | Charlotte | NC | USA | 35.21401 | -80.94313 | |
68 | CMH | Columbus | OH | USA | 39.99799 | -82.89188 | |
69 | CMI | Champaign/Urbana | IL | USA | 40.03925 | -88.27806 | |
70 | CMX | Hancock | MI | USA | 47.16842 | -88.48906 | |
71 | CNY | Moab | UT | USA | 38.75496 | -109.75484 | |
72 | COD | Cody | WY | USA | 44.52019 | -109.0238 | |
73 | COS | Colorado Springs | CO | USA | 38.80581 | -104.70025 | |
74 | COU | Columbia | MO | USA | 38.81809 | -92.21963 | |
75 | CPR | Casper | WY | USA | 42.90836 | -106.46447 | |
76 | CRP | Corpus Christi | TX | USA | 27.77036 | -97.50122 | |
77 | CRW | Charleston | WV | USA | 38.37315 | -81.59319 | |
78 | CSG | Columbus | GA | USA | 32.51633 | -84.93886 | |
79 | CVG | Covington | KY | USA | 39.04614 | -84.66217 | |
80 | CWA | Mosinee | WI | USA | 44.77762 | -89.66678 | |
81 | DAB | Daytona Beach | FL | USA | 29.17992 | -81.05806 | |
82 | DAL | Dallas | TX | USA | 32.84711 | -96.85177 | |
83 | DAY | Dayton | OH | USA | 39.90238 | -84.21938 | |
84 | DBQ | Dubuque | IA | USA | 42.40296 | -90.70917 | |
85 | DCA | Arlington | VA | USA | 38.85208 | -77.03772 | |
86 | DEN | Denver | CO | USA | 39.85841 | -104.667 | |
87 | DFW | Dallas-Fort Worth | TX | USA | 32.89595 | -97.0372 | |
88 | DHN | Dothan | AL | USA | 31.32134 | -85.44963 | |
89 | DIK | Dickinson | ND | USA | 46.79739 | -102.80195 | |
90 | DLG | Dillingham | AK | USA | 59.04541 | -158.50334 | |
91 | DLH | Duluth | MN | USA | 46.84209 | -92.19365 | |
92 | DRO | Durango | CO | USA | 37.15152 | -107.75377 | |
93 | DSM | Des Moines | IA | USA | 41.53493 | -93.66068 | |
94 | DTW | Detroit | MI | USA | 42.21206 | -83.34884 | |
95 | DVL | Devils Lake | ND | USA | 48.11425 | -98.90878 | |
96 | EAU | Eau Claire | WI | USA | 44.86526 | -91.48507 | |
97 | ECP | Panama City | FL | USA | [null] | [null] | |
98 | EGE | Eagle | CO | USA | 39.64257 | -106.9177 | |
99 | EKO | Elko | NV | USA | 40.82493 | -115.7917 | |
100 | ELM | Elmira | NY | USA | 42.15991 | -76.89144 |
All vDataFrame functions are available for this imported table. For example, we can get all the column names:
[ ]:
# Get all columns of the dataset
tab_data.get_columns()
Or the column data types:
[ ]:
# Get data types of all columns inside the dataset
tab_data.dtypes()
Or the count of the datapoints:
[6]:
# Counting all elements inside each column
tab_data.count()
[6]:
count | |
"IATA_CODE" | 322.0 |
"AIRPORT" | 322.0 |
"CITY" | 322.0 |
"STATE" | 322.0 |
"COUNTRY" | 322.0 |
"LATITUDE" | 319.0 |
"LONGITUDE" | 319.0 |
Note: Every time we perform these calculations or call the vDataFrame, it runs the SQL query to fetch all the data from the external database. After retrieving the entire table, the operations are computed by Vertica. In order to push the queries to a remote database, we can use the option “sql_push_ext”. When we create a vDataFrame with this option activated, all the aggregations are done on the external database using SQL.
[7]:
# Creating a vDataFrame and setting sql_push_ext to True, which tries
# to push SQL queries to external database (where possible).
Ext_Table=vp.vDataFrame(input_relation = "airports",
external = True,
symbol = "&",
sql_push_ext = True)
Ext_Table
[7]:
Abc IATA_CODEVarchar(3) | Abc Varchar(100) | Abc CITYVarchar(60) | Abc STATEVarchar(2) | Abc COUNTRYVarchar(100) | 🌎 LATITUDENumeric(30) | 🌎 LONGITUDENumeric(30) | |
1 | ABE | Allentown | PA | USA | 40.65236 | -75.4404 | |
2 | ABI | Abilene | TX | USA | 32.41132 | -99.6819 | |
3 | ABQ | Albuquerque | NM | USA | 35.04022 | -106.60919 | |
4 | ABR | Aberdeen | SD | USA | 45.44906 | -98.42183 | |
5 | ABY | Albany | GA | USA | 31.53552 | -84.19447 | |
6 | ACK | Nantucket | MA | USA | 41.25305 | -70.06018 | |
7 | ACT | Waco | TX | USA | 31.61129 | -97.23052 | |
8 | ACV | Arcata/Eureka | CA | USA | 40.97812 | -124.10862 | |
9 | ACY | Atlantic City | NJ | USA | 39.45758 | -74.57717 | |
10 | ADK | Adak | AK | USA | 51.87796 | -176.64603 | |
11 | ADQ | Kodiak | AK | USA | 57.74997 | -152.49386 | |
12 | AEX | Alexandria | LA | USA | 31.32737 | -92.54856 | |
13 | AGS | Augusta | GA | USA | 33.36996 | -81.9645 | |
14 | AKN | King Salmon | AK | USA | 58.6768 | -156.64922 | |
15 | ALB | Albany | NY | USA | 42.74812 | -73.80298 | |
16 | ALO | Waterloo | IA | USA | 42.55708 | -92.40034 | |
17 | AMA | Amarillo | TX | USA | 35.21937 | -101.70593 | |
18 | ANC | Anchorage | AK | USA | 61.17432 | -149.99619 | |
19 | APN | Alpena | MI | USA | 45.07807 | -83.56029 | |
20 | ASE | Aspen | CO | USA | 39.22316 | -106.86885 | |
21 | ATL | Atlanta | GA | USA | 33.64044 | -84.42694 | |
22 | ATW | Appleton | WI | USA | 44.25741 | -88.51948 | |
23 | AUS | Austin | TX | USA | 30.19453 | -97.66987 | |
24 | AVL | Asheville | NC | USA | 35.43619 | -82.54181 | |
25 | AVP | Wilkes-Barre/Scranton | PA | USA | 41.33815 | -75.72427 | |
26 | AZO | Kalamazoo | MI | USA | 42.23488 | -85.55206 | |
27 | BDL | Windsor Locks | CT | USA | 41.93887 | -72.68323 | |
28 | BET | Bethel | AK | USA | 60.77978 | -161.838 | |
29 | BFL | Bakersfield | CA | USA | 35.4336 | -119.05677 | |
30 | BGM | Binghamton | NY | USA | 42.20848 | -75.97961 | |
31 | BGR | Bangor | ME | USA | 44.80744 | -68.82814 | |
32 | BHM | Birmingham | AL | USA | 33.56294 | -86.75355 | |
33 | BIL | Billings | MT | USA | 45.80766 | -108.54286 | |
34 | BIS | Bismarck | ND | USA | 46.77411 | -100.74672 | |
35 | BJI | Bemidji | MN | USA | 47.50942 | -94.93372 | |
36 | BLI | Bellingham | WA | USA | 48.79275 | -122.53753 | |
37 | BMI | Bloomington | IL | USA | 40.47799 | -88.91595 | |
38 | BNA | Nashville | TN | USA | 36.12448 | -86.67818 | |
39 | BOI | Boise | ID | USA | 43.56444 | -116.22278 | |
40 | BOS | Boston | MA | USA | 42.36435 | -71.00518 | |
41 | BPT | Beaumont/Port Arthur | TX | USA | 29.95083 | -94.02069 | |
42 | BQK | Brunswick | GA | USA | 31.25903 | -81.46631 | |
43 | BQN | Aguadilla | PR | USA | 18.49486 | -67.12944 | |
44 | BRD | Brainerd | MN | USA | 46.39786 | -94.13723 | |
45 | BRO | Brownsville | TX | USA | 25.90683 | -97.42586 | |
46 | BRW | Barrow | AK | USA | 71.28545 | -156.766 | |
47 | BTM | Butte | MT | USA | 45.9548 | -112.49746 | |
48 | BTR | Baton Rouge | LA | USA | 30.53316 | -91.14963 | |
49 | BTV | Burlington | VT | USA | 44.473 | -73.15031 | |
50 | BUF | Buffalo | NY | USA | 42.94052 | -78.73217 | |
51 | BUR | Burbank | CA | USA | 34.20062 | -118.3585 | |
52 | BWI | Baltimore | MD | USA | 39.1754 | -76.6682 | |
53 | BZN | Bozeman | MT | USA | 45.7769 | -111.15301 | |
54 | CAE | Columbia | SC | USA | 33.93884 | -81.11954 | |
55 | CAK | Akron | OH | USA | 40.91631 | -81.44247 | |
56 | CDC | Cedar City | UT | USA | 37.70097 | -113.09858 | |
57 | CDV | Cordova | AK | USA | 60.49183 | -145.47765 | |
58 | CEC | Crescent City | CA | USA | 41.78016 | -124.23653 | |
59 | CHA | Chattanooga | TN | USA | 35.03527 | -85.20379 | |
60 | CHO | Charlottesville | VA | USA | 38.13864 | -78.45286 | |
61 | CHS | Charleston | SC | USA | 32.89865 | -80.04051 | |
62 | CID | Cedar Rapids | IA | USA | 41.88459 | -91.71087 | |
63 | CIU | Sault Ste. Marie | MI | USA | 46.25075 | -84.47239 | |
64 | CLD | San Diego | CA | USA | 33.12723 | -117.27873 | |
65 | CLE | Cleveland | OH | USA | 41.41089 | -81.8494 | |
66 | CLL | College Station | TX | USA | 30.58859 | -96.36382 | |
67 | CLT | Charlotte | NC | USA | 35.21401 | -80.94313 | |
68 | CMH | Columbus | OH | USA | 39.99799 | -82.89188 | |
69 | CMI | Champaign/Urbana | IL | USA | 40.03925 | -88.27806 | |
70 | CMX | Hancock | MI | USA | 47.16842 | -88.48906 | |
71 | CNY | Moab | UT | USA | 38.75496 | -109.75484 | |
72 | COD | Cody | WY | USA | 44.52019 | -109.0238 | |
73 | COS | Colorado Springs | CO | USA | 38.80581 | -104.70025 | |
74 | COU | Columbia | MO | USA | 38.81809 | -92.21963 | |
75 | CPR | Casper | WY | USA | 42.90836 | -106.46447 | |
76 | CRP | Corpus Christi | TX | USA | 27.77036 | -97.50122 | |
77 | CRW | Charleston | WV | USA | 38.37315 | -81.59319 | |
78 | CSG | Columbus | GA | USA | 32.51633 | -84.93886 | |
79 | CVG | Covington | KY | USA | 39.04614 | -84.66217 | |
80 | CWA | Mosinee | WI | USA | 44.77762 | -89.66678 | |
81 | DAB | Daytona Beach | FL | USA | 29.17992 | -81.05806 | |
82 | DAL | Dallas | TX | USA | 32.84711 | -96.85177 | |
83 | DAY | Dayton | OH | USA | 39.90238 | -84.21938 | |
84 | DBQ | Dubuque | IA | USA | 42.40296 | -90.70917 | |
85 | DCA | Arlington | VA | USA | 38.85208 | -77.03772 | |
86 | DEN | Denver | CO | USA | 39.85841 | -104.667 | |
87 | DFW | Dallas-Fort Worth | TX | USA | 32.89595 | -97.0372 | |
88 | DHN | Dothan | AL | USA | 31.32134 | -85.44963 | |
89 | DIK | Dickinson | ND | USA | 46.79739 | -102.80195 | |
90 | DLG | Dillingham | AK | USA | 59.04541 | -158.50334 | |
91 | DLH | Duluth | MN | USA | 46.84209 | -92.19365 | |
92 | DRO | Durango | CO | USA | 37.15152 | -107.75377 | |
93 | DSM | Des Moines | IA | USA | 41.53493 | -93.66068 | |
94 | DTW | Detroit | MI | USA | 42.21206 | -83.34884 | |
95 | DVL | Devils Lake | ND | USA | 48.11425 | -98.90878 | |
96 | EAU | Eau Claire | WI | USA | 44.86526 | -91.48507 | |
97 | ECP | Panama City | FL | USA | [null] | [null] | |
98 | EGE | Eagle | CO | USA | 39.64257 | -106.9177 | |
99 | EKO | Elko | NV | USA | 40.82493 | -115.7917 | |
100 | ELM | Elmira | NY | USA | 42.15991 | -76.89144 |
If we look at the SQL generated in background, we can see that it pushes the aggregation query to the database.
[8]:
# Turning on SQL output to view the queries
vp.set_option("sql_on",True)
Let’s look at the count query again, and see how VerticaPy is pushing it to the external database.
[9]:
# Counting elements in each column
Ext_Table.count()
Computing the different aggregations.
DBLINK(USING PARAMETERS cid='pgdb', query=' SELECT COUNT("IATA_CODE"), COUNT("AIRPORT"), COUNT("CITY"), COUNT("STATE"), COUNT("COUNTRY"), COUNT("LATITUDE"), COUNT("LONGITUDE") FROM ( SELECT "IATA_CODE", "AIRPORT", "CITY", "STATE", "COUNTRY", "LATITUDE", "LONGITUDE" FROM ( SELECT * FROM airports) VERTICAPY_SUBTABLE) VERTICAPY_SUBTABLE LIMIT 1', rowset=500) OVER ()
[9]:
count | |
"IATA_CODE" | 322.0 |
"AIRPORT" | 322.0 |
"CITY" | 322.0 |
"STATE" | 322.0 |
"COUNTRY" | 322.0 |
"LATITUDE" | 319.0 |
"LONGITUDE" | 319.0 |
Let’s also look at the “min” method:
[10]:
# Finding minimum in the ID column of Ext_Table
Ext_Table["LATITUDE"].min()
Computing the different aggregations.
DBLINK(USING PARAMETERS cid='pgdb', query=' SELECT MIN("LATITUDE") FROM ( SELECT "IATA_CODE", "AIRPORT", "CITY", "STATE", "COUNTRY", "LATITUDE", "LONGITUDE" FROM ( SELECT * FROM airports) VERTICAPY_SUBTABLE) VERTICAPY_SUBTABLE LIMIT 1', rowset=500) OVER ()
[10]:
13.48345
For the above examples, the queries were pushed to the external database.
If the function is unique to Vertica, it automatically fetches the data from the external database to compute on the Vertica server.
Let’s try an example with the describe function, which is a unique Vertica function.
[11]:
# Describe the main attributes of numerical columns in the Ext_table
Ext_Table.describe()
Getting the version.
/*+LABEL('utilities.version')*/ version()
Computing the descriptive statistics of all numerical columns using SUMMARIZE_NUMCOL.
/*+LABEL('vDataframe.describe')*/ SUMMARIZE_NUMCOL("LATITUDE", "LONGITUDE") OVER ()
FROM
(
SELECT
"IATA_CODE",
"AIRPORT",
"CITY",
"STATE",
"COUNTRY",
"LATITUDE",
"LONGITUDE"
FROM
(
SELECT
DBLINK(USING PARAMETERS cid='pgdb', query=' SELECT * FROM airports', rowset=500) OVER ())
VERTICAPY_SUBTABLE)
VERTICAPY_SUBTABLE
[11]:
count | mean | std | min | approx_25% | approx_50% | approx_75% | max | |
"LATITUDE" | 319 | 38.9812439184953 | 8.61673558101804 | 13.48345 | 33.65204 | 39.29761 | 43.154675 | 71.28545 |
"LONGITUDE" | 319 | -98.3789644514107 | 21.5234920464981 | -176.64603 | -110.839385 | -93.40307 | -82.722995 | -64.79856 |
We can see that the data was fetched from the external database to be computed in Vertica.
(Now we can turn off SQL display).
[12]:
# Turning off SQL display
vp.set_option("sql_on",
False)
Using SQL Magic Cells#
[2]:
# Load extension for running SQL magic cells
%load_ext verticapy.sql
We can use magic cells to call external tables using special characters like “$$$” and “%%%”. If we have multiple external databases, we can specify special characters for each.
This makes writing queries a lot more convenient and visually appealing!
Now we will try to get fetch data from our external database “pgdb, whose special character is “&”.
[14]:
%%sql
/* Getting all data from airports table which is placed in the PostgreSQL database represented by "&". */
SELECT * FROM &&& airports &&&;
[14]:
Abc IATA_CODEVarchar(3) | Abc Varchar(100) | Abc CITYVarchar(60) | Abc STATEVarchar(2) | Abc COUNTRYVarchar(100) | 🌎 LATITUDENumeric(30) | 🌎 LONGITUDENumeric(30) | |
1 | ABE | Allentown | PA | USA | 40.65236 | -75.4404 | |
2 | ABI | Abilene | TX | USA | 32.41132 | -99.6819 | |
3 | ABQ | Albuquerque | NM | USA | 35.04022 | -106.60919 | |
4 | ABR | Aberdeen | SD | USA | 45.44906 | -98.42183 | |
5 | ABY | Albany | GA | USA | 31.53552 | -84.19447 | |
6 | ACK | Nantucket | MA | USA | 41.25305 | -70.06018 | |
7 | ACT | Waco | TX | USA | 31.61129 | -97.23052 | |
8 | ACV | Arcata/Eureka | CA | USA | 40.97812 | -124.10862 | |
9 | ACY | Atlantic City | NJ | USA | 39.45758 | -74.57717 | |
10 | ADK | Adak | AK | USA | 51.87796 | -176.64603 | |
11 | ADQ | Kodiak | AK | USA | 57.74997 | -152.49386 | |
12 | AEX | Alexandria | LA | USA | 31.32737 | -92.54856 | |
13 | AGS | Augusta | GA | USA | 33.36996 | -81.9645 | |
14 | AKN | King Salmon | AK | USA | 58.6768 | -156.64922 | |
15 | ALB | Albany | NY | USA | 42.74812 | -73.80298 | |
16 | ALO | Waterloo | IA | USA | 42.55708 | -92.40034 | |
17 | AMA | Amarillo | TX | USA | 35.21937 | -101.70593 | |
18 | ANC | Anchorage | AK | USA | 61.17432 | -149.99619 | |
19 | APN | Alpena | MI | USA | 45.07807 | -83.56029 | |
20 | ASE | Aspen | CO | USA | 39.22316 | -106.86885 | |
21 | ATL | Atlanta | GA | USA | 33.64044 | -84.42694 | |
22 | ATW | Appleton | WI | USA | 44.25741 | -88.51948 | |
23 | AUS | Austin | TX | USA | 30.19453 | -97.66987 | |
24 | AVL | Asheville | NC | USA | 35.43619 | -82.54181 | |
25 | AVP | Wilkes-Barre/Scranton | PA | USA | 41.33815 | -75.72427 | |
26 | AZO | Kalamazoo | MI | USA | 42.23488 | -85.55206 | |
27 | BDL | Windsor Locks | CT | USA | 41.93887 | -72.68323 | |
28 | BET | Bethel | AK | USA | 60.77978 | -161.838 | |
29 | BFL | Bakersfield | CA | USA | 35.4336 | -119.05677 | |
30 | BGM | Binghamton | NY | USA | 42.20848 | -75.97961 | |
31 | BGR | Bangor | ME | USA | 44.80744 | -68.82814 | |
32 | BHM | Birmingham | AL | USA | 33.56294 | -86.75355 | |
33 | BIL | Billings | MT | USA | 45.80766 | -108.54286 | |
34 | BIS | Bismarck | ND | USA | 46.77411 | -100.74672 | |
35 | BJI | Bemidji | MN | USA | 47.50942 | -94.93372 | |
36 | BLI | Bellingham | WA | USA | 48.79275 | -122.53753 | |
37 | BMI | Bloomington | IL | USA | 40.47799 | -88.91595 | |
38 | BNA | Nashville | TN | USA | 36.12448 | -86.67818 | |
39 | BOI | Boise | ID | USA | 43.56444 | -116.22278 | |
40 | BOS | Boston | MA | USA | 42.36435 | -71.00518 | |
41 | BPT | Beaumont/Port Arthur | TX | USA | 29.95083 | -94.02069 | |
42 | BQK | Brunswick | GA | USA | 31.25903 | -81.46631 | |
43 | BQN | Aguadilla | PR | USA | 18.49486 | -67.12944 | |
44 | BRD | Brainerd | MN | USA | 46.39786 | -94.13723 | |
45 | BRO | Brownsville | TX | USA | 25.90683 | -97.42586 | |
46 | BRW | Barrow | AK | USA | 71.28545 | -156.766 | |
47 | BTM | Butte | MT | USA | 45.9548 | -112.49746 | |
48 | BTR | Baton Rouge | LA | USA | 30.53316 | -91.14963 | |
49 | BTV | Burlington | VT | USA | 44.473 | -73.15031 | |
50 | BUF | Buffalo | NY | USA | 42.94052 | -78.73217 | |
51 | BUR | Burbank | CA | USA | 34.20062 | -118.3585 | |
52 | BWI | Baltimore | MD | USA | 39.1754 | -76.6682 | |
53 | BZN | Bozeman | MT | USA | 45.7769 | -111.15301 | |
54 | CAE | Columbia | SC | USA | 33.93884 | -81.11954 | |
55 | CAK | Akron | OH | USA | 40.91631 | -81.44247 | |
56 | CDC | Cedar City | UT | USA | 37.70097 | -113.09858 | |
57 | CDV | Cordova | AK | USA | 60.49183 | -145.47765 | |
58 | CEC | Crescent City | CA | USA | 41.78016 | -124.23653 | |
59 | CHA | Chattanooga | TN | USA | 35.03527 | -85.20379 | |
60 | CHO | Charlottesville | VA | USA | 38.13864 | -78.45286 | |
61 | CHS | Charleston | SC | USA | 32.89865 | -80.04051 | |
62 | CID | Cedar Rapids | IA | USA | 41.88459 | -91.71087 | |
63 | CIU | Sault Ste. Marie | MI | USA | 46.25075 | -84.47239 | |
64 | CLD | San Diego | CA | USA | 33.12723 | -117.27873 | |
65 | CLE | Cleveland | OH | USA | 41.41089 | -81.8494 | |
66 | CLL | College Station | TX | USA | 30.58859 | -96.36382 | |
67 | CLT | Charlotte | NC | USA | 35.21401 | -80.94313 | |
68 | CMH | Columbus | OH | USA | 39.99799 | -82.89188 | |
69 | CMI | Champaign/Urbana | IL | USA | 40.03925 | -88.27806 | |
70 | CMX | Hancock | MI | USA | 47.16842 | -88.48906 | |
71 | CNY | Moab | UT | USA | 38.75496 | -109.75484 | |
72 | COD | Cody | WY | USA | 44.52019 | -109.0238 | |
73 | COS | Colorado Springs | CO | USA | 38.80581 | -104.70025 | |
74 | COU | Columbia | MO | USA | 38.81809 | -92.21963 | |
75 | CPR | Casper | WY | USA | 42.90836 | -106.46447 | |
76 | CRP | Corpus Christi | TX | USA | 27.77036 | -97.50122 | |
77 | CRW | Charleston | WV | USA | 38.37315 | -81.59319 | |
78 | CSG | Columbus | GA | USA | 32.51633 | -84.93886 | |
79 | CVG | Covington | KY | USA | 39.04614 | -84.66217 | |
80 | CWA | Mosinee | WI | USA | 44.77762 | -89.66678 | |
81 | DAB | Daytona Beach | FL | USA | 29.17992 | -81.05806 | |
82 | DAL | Dallas | TX | USA | 32.84711 | -96.85177 | |
83 | DAY | Dayton | OH | USA | 39.90238 | -84.21938 | |
84 | DBQ | Dubuque | IA | USA | 42.40296 | -90.70917 | |
85 | DCA | Arlington | VA | USA | 38.85208 | -77.03772 | |
86 | DEN | Denver | CO | USA | 39.85841 | -104.667 | |
87 | DFW | Dallas-Fort Worth | TX | USA | 32.89595 | -97.0372 | |
88 | DHN | Dothan | AL | USA | 31.32134 | -85.44963 | |
89 | DIK | Dickinson | ND | USA | 46.79739 | -102.80195 | |
90 | DLG | Dillingham | AK | USA | 59.04541 | -158.50334 | |
91 | DLH | Duluth | MN | USA | 46.84209 | -92.19365 | |
92 | DRO | Durango | CO | USA | 37.15152 | -107.75377 | |
93 | DSM | Des Moines | IA | USA | 41.53493 | -93.66068 | |
94 | DTW | Detroit | MI | USA | 42.21206 | -83.34884 | |
95 | DVL | Devils Lake | ND | USA | 48.11425 | -98.90878 | |
96 | EAU | Eau Claire | WI | USA | 44.86526 | -91.48507 | |
97 | ECP | Panama City | FL | USA | [null] | [null] | |
98 | EGE | Eagle | CO | USA | 39.64257 | -106.9177 | |
99 | EKO | Elko | NV | USA | 40.82493 | -115.7917 | |
100 | ELM | Elmira | NY | USA | 42.15991 | -76.89144 |
To perform all regular queries, all we need to do is call the table with its name inside three special characters.
We’ll now try out some queries:
Count the elements inside the table:
[15]:
%%sql
/* Counting all elements inside the airports table in PostgreSQL. */
SELECT COUNT(*) FROM &&& airports &&&;
[15]:
123 COUNTInteger | |
1 | 322 |
Find the IATA_CODE where CITY is “Allentown”:
[16]:
%%sql
/* Finding IATA_CODE where the CITY is "Allentown" in the airports table. */
SELECT IATA_CODE
FROM &&& airports &&&
WHERE CITY='Allentown';
[16]:
Abc IATA_CODEVarchar(3) | |
1 | ABE |
Note: Any query that we write inside the “&&&” signs is also sent to the external database to be run.
So, instead of just calling the whole table, we can query it using the same special character padding.
For example, let’s select all elements inside the airports table:
[17]:
%%sql
/* Getting all data from airports table which is placed in the PostgreSQL database represented by "$". */
&&& SELECT * FROM airports &&&;
[17]:
Abc IATA_CODEVarchar(3) | Abc Varchar(100) | Abc CITYVarchar(60) | Abc STATEVarchar(2) | Abc COUNTRYVarchar(100) | 🌎 LATITUDENumeric(30) | 🌎 LONGITUDENumeric(30) | |
1 | ABE | Allentown | PA | USA | 40.65236 | -75.4404 | |
2 | ABI | Abilene | TX | USA | 32.41132 | -99.6819 | |
3 | ABQ | Albuquerque | NM | USA | 35.04022 | -106.60919 | |
4 | ABR | Aberdeen | SD | USA | 45.44906 | -98.42183 | |
5 | ABY | Albany | GA | USA | 31.53552 | -84.19447 | |
6 | ACK | Nantucket | MA | USA | 41.25305 | -70.06018 | |
7 | ACT | Waco | TX | USA | 31.61129 | -97.23052 | |
8 | ACV | Arcata/Eureka | CA | USA | 40.97812 | -124.10862 | |
9 | ACY | Atlantic City | NJ | USA | 39.45758 | -74.57717 | |
10 | ADK | Adak | AK | USA | 51.87796 | -176.64603 | |
11 | ADQ | Kodiak | AK | USA | 57.74997 | -152.49386 | |
12 | AEX | Alexandria | LA | USA | 31.32737 | -92.54856 | |
13 | AGS | Augusta | GA | USA | 33.36996 | -81.9645 | |
14 | AKN | King Salmon | AK | USA | 58.6768 | -156.64922 | |
15 | ALB | Albany | NY | USA | 42.74812 | -73.80298 | |
16 | ALO | Waterloo | IA | USA | 42.55708 | -92.40034 | |
17 | AMA | Amarillo | TX | USA | 35.21937 | -101.70593 | |
18 | ANC | Anchorage | AK | USA | 61.17432 | -149.99619 | |
19 | APN | Alpena | MI | USA | 45.07807 | -83.56029 | |
20 | ASE | Aspen | CO | USA | 39.22316 | -106.86885 | |
21 | ATL | Atlanta | GA | USA | 33.64044 | -84.42694 | |
22 | ATW | Appleton | WI | USA | 44.25741 | -88.51948 | |
23 | AUS | Austin | TX | USA | 30.19453 | -97.66987 | |
24 | AVL | Asheville | NC | USA | 35.43619 | -82.54181 | |
25 | AVP | Wilkes-Barre/Scranton | PA | USA | 41.33815 | -75.72427 | |
26 | AZO | Kalamazoo | MI | USA | 42.23488 | -85.55206 | |
27 | BDL | Windsor Locks | CT | USA | 41.93887 | -72.68323 | |
28 | BET | Bethel | AK | USA | 60.77978 | -161.838 | |
29 | BFL | Bakersfield | CA | USA | 35.4336 | -119.05677 | |
30 | BGM | Binghamton | NY | USA | 42.20848 | -75.97961 | |
31 | BGR | Bangor | ME | USA | 44.80744 | -68.82814 | |
32 | BHM | Birmingham | AL | USA | 33.56294 | -86.75355 | |
33 | BIL | Billings | MT | USA | 45.80766 | -108.54286 | |
34 | BIS | Bismarck | ND | USA | 46.77411 | -100.74672 | |
35 | BJI | Bemidji | MN | USA | 47.50942 | -94.93372 | |
36 | BLI | Bellingham | WA | USA | 48.79275 | -122.53753 | |
37 | BMI | Bloomington | IL | USA | 40.47799 | -88.91595 | |
38 | BNA | Nashville | TN | USA | 36.12448 | -86.67818 | |
39 | BOI | Boise | ID | USA | 43.56444 | -116.22278 | |
40 | BOS | Boston | MA | USA | 42.36435 | -71.00518 | |
41 | BPT | Beaumont/Port Arthur | TX | USA | 29.95083 | -94.02069 | |
42 | BQK | Brunswick | GA | USA | 31.25903 | -81.46631 | |
43 | BQN | Aguadilla | PR | USA | 18.49486 | -67.12944 | |
44 | BRD | Brainerd | MN | USA | 46.39786 | -94.13723 | |
45 | BRO | Brownsville | TX | USA | 25.90683 | -97.42586 | |
46 | BRW | Barrow | AK | USA | 71.28545 | -156.766 | |
47 | BTM | Butte | MT | USA | 45.9548 | -112.49746 | |
48 | BTR | Baton Rouge | LA | USA | 30.53316 | -91.14963 | |
49 | BTV | Burlington | VT | USA | 44.473 | -73.15031 | |
50 | BUF | Buffalo | NY | USA | 42.94052 | -78.73217 | |
51 | BUR | Burbank | CA | USA | 34.20062 | -118.3585 | |
52 | BWI | Baltimore | MD | USA | 39.1754 | -76.6682 | |
53 | BZN | Bozeman | MT | USA | 45.7769 | -111.15301 | |
54 | CAE | Columbia | SC | USA | 33.93884 | -81.11954 | |
55 | CAK | Akron | OH | USA | 40.91631 | -81.44247 | |
56 | CDC | Cedar City | UT | USA | 37.70097 | -113.09858 | |
57 | CDV | Cordova | AK | USA | 60.49183 | -145.47765 | |
58 | CEC | Crescent City | CA | USA | 41.78016 | -124.23653 | |
59 | CHA | Chattanooga | TN | USA | 35.03527 | -85.20379 | |
60 | CHO | Charlottesville | VA | USA | 38.13864 | -78.45286 | |
61 | CHS | Charleston | SC | USA | 32.89865 | -80.04051 | |
62 | CID | Cedar Rapids | IA | USA | 41.88459 | -91.71087 | |
63 | CIU | Sault Ste. Marie | MI | USA | 46.25075 | -84.47239 | |
64 | CLD | San Diego | CA | USA | 33.12723 | -117.27873 | |
65 | CLE | Cleveland | OH | USA | 41.41089 | -81.8494 | |
66 | CLL | College Station | TX | USA | 30.58859 | -96.36382 | |
67 | CLT | Charlotte | NC | USA | 35.21401 | -80.94313 | |
68 | CMH | Columbus | OH | USA | 39.99799 | -82.89188 | |
69 | CMI | Champaign/Urbana | IL | USA | 40.03925 | -88.27806 | |
70 | CMX | Hancock | MI | USA | 47.16842 | -88.48906 | |
71 | CNY | Moab | UT | USA | 38.75496 | -109.75484 | |
72 | COD | Cody | WY | USA | 44.52019 | -109.0238 | |
73 | COS | Colorado Springs | CO | USA | 38.80581 | -104.70025 | |
74 | COU | Columbia | MO | USA | 38.81809 | -92.21963 | |
75 | CPR | Casper | WY | USA | 42.90836 | -106.46447 | |
76 | CRP | Corpus Christi | TX | USA | 27.77036 | -97.50122 | |
77 | CRW | Charleston | WV | USA | 38.37315 | -81.59319 | |
78 | CSG | Columbus | GA | USA | 32.51633 | -84.93886 | |
79 | CVG | Covington | KY | USA | 39.04614 | -84.66217 | |
80 | CWA | Mosinee | WI | USA | 44.77762 | -89.66678 | |
81 | DAB | Daytona Beach | FL | USA | 29.17992 | -81.05806 | |
82 | DAL | Dallas | TX | USA | 32.84711 | -96.85177 | |
83 | DAY | Dayton | OH | USA | 39.90238 | -84.21938 | |
84 | DBQ | Dubuque | IA | USA | 42.40296 | -90.70917 | |
85 | DCA | Arlington | VA | USA | 38.85208 | -77.03772 | |
86 | DEN | Denver | CO | USA | 39.85841 | -104.667 | |
87 | DFW | Dallas-Fort Worth | TX | USA | 32.89595 | -97.0372 | |
88 | DHN | Dothan | AL | USA | 31.32134 | -85.44963 | |
89 | DIK | Dickinson | ND | USA | 46.79739 | -102.80195 | |
90 | DLG | Dillingham | AK | USA | 59.04541 | -158.50334 | |
91 | DLH | Duluth | MN | USA | 46.84209 | -92.19365 | |
92 | DRO | Durango | CO | USA | 37.15152 | -107.75377 | |
93 | DSM | Des Moines | IA | USA | 41.53493 | -93.66068 | |
94 | DTW | Detroit | MI | USA | 42.21206 | -83.34884 | |
95 | DVL | Devils Lake | ND | USA | 48.11425 | -98.90878 | |
96 | EAU | Eau Claire | WI | USA | 44.86526 | -91.48507 | |
97 | ECP | Panama City | FL | USA | [null] | [null] | |
98 | EGE | Eagle | CO | USA | 39.64257 | -106.9177 | |
99 | EKO | Elko | NV | USA | 40.82493 | -115.7917 | |
100 | ELM | Elmira | NY | USA | 42.15991 | -76.89144 |
Now we’ll run a search query to find a particular id:
[18]:
%%sql
/* Finding IATA_CODE where the CITY is "Allentown" in the airports table. */
&&& SELECT "IATA_CODE" FROM airports WHERE "CITY"='Allentown' &&&;
[18]:
Abc IATA_CODEVarchar(3) | |
1 | ABE |
We can also insert a new entry into the airports table, which is placed in the postgreSQL database represented by “&”:
[19]:
%%sql
/* Inserting an entry into the airportss table which is placed in the postgreSQL database represented by "&". */
&&&
INSERT INTO airports
("IATA_CODE", "AIRPORT", "CITY", "STATE", "COUNTRY", "LATITUDE", "LONGITUDE")
VALUES ('MXX' , 'Midway Airport', 'Chicago', 'IL', 'USA', 66.60, 35.00);
&&&
undefined
Connect Multiple Databases#
You can connect and use multiple datasets from different databases.
In this example we will get:
Airline data from PostgreSQL
Airport data from MySQL
Flights data from Vertica
The datasets can be found here.
Airline Data in PostgreSQL#
We can set up a new connection in just one line by referencing the alias inside the connection files. As before, we will provide the special character symbol that is used to invoke the connection.
[3]:
# Setting up a connection with a database given an alias "pgdb"
vp.set_external_connection(cid="pgdb",
rowset=500,
symbol="$")
Let’s look at the airline table that we have in our postgreSQL database.
[4]:
%%sql
/* Fetch all the data from the table airports in "pgdb" database. */
SELECT * FROM $$$ airline $$$;
[4]:
Abc IATA_CODEChar(2) | Abc AIRLINEVarchar(1024) | |
1 | UA | United Air Lines Inc. |
2 | AA | American Airlines Inc. |
3 | US | US Airways Inc. |
4 | F9 | Frontier Airlines Inc. |
5 | B6 | JetBlue Airways |
6 | OO | Skywest Airlines Inc. |
7 | AS | Alaska Airlines Inc. |
8 | NK | Spirit Air Lines |
9 | WN | Southwest Airlines Co. |
10 | DL | Delta Air Lines Inc. |
11 | EV | Atlantic Southeast Airlines |
12 | HA | Hawaiian Airlines Inc. |
13 | MQ | American Eagle Airlines Inc. |
14 | VX | Virgin America |
15 | UA | United Air Lines Inc. |
16 | AA | American Airlines Inc. |
17 | US | US Airways Inc. |
18 | F9 | Frontier Airlines Inc. |
19 | B6 | JetBlue Airways |
20 | OO | Skywest Airlines Inc. |
21 | AS | Alaska Airlines Inc. |
22 | NK | Spirit Air Lines |
23 | WN | Southwest Airlines Co. |
24 | DL | Delta Air Lines Inc. |
25 | EV | Atlantic Southeast Airlines |
26 | HA | Hawaiian Airlines Inc. |
27 | MQ | American Eagle Airlines Inc. |
28 | VX | Virgin America |
Airports Data in MySQL#
We can create another new connection by providing the cid reference for our MySQL database. We’ll also provide a unique special character, which is not used for any other connection.
[5]:
# Setting up a connection with a database given an alias "mysql"
vp.set_external_connection(cid="mysql",
rowset=500,
symbol="&")
Let’s take a look at the airports table that we have in our MySQL database.
[6]:
%%sql
/* Fetch all the data from the table airports in "mysql" database */
SELECT * FROM &&& airports &&&;
[6]:
Abc IATA_CODEVarchar(3) | Abc Varchar(100) | Abc CITYVarchar(60) | Abc STATEVarchar(2) | Abc COUNTRYVarchar(100) | 🌎 LATITUDENumeric(30) | 🌎 LONGITUDENumeric(30) | |
1 | ABE | Allentown | PA | USA | 40.65236 | -75.4404 | |
2 | ABI | Abilene | TX | USA | 32.41132 | -99.6819 | |
3 | ABQ | Albuquerque | NM | USA | 35.04022 | -106.60919 | |
4 | ABR | Aberdeen | SD | USA | 45.44906 | -98.42183 | |
5 | ABY | Albany | GA | USA | 31.53552 | -84.19447 | |
6 | ACK | Nantucket | MA | USA | 41.25305 | -70.06018 | |
7 | ACT | Waco | TX | USA | 31.61129 | -97.23052 | |
8 | ACV | Arcata/Eureka | CA | USA | 40.97812 | -124.10862 | |
9 | ACY | Atlantic City | NJ | USA | 39.45758 | -74.57717 | |
10 | ADK | Adak | AK | USA | 51.87796 | -176.64603 | |
11 | ADQ | Kodiak | AK | USA | 57.74997 | -152.49386 | |
12 | AEX | Alexandria | LA | USA | 31.32737 | -92.54856 | |
13 | AGS | Augusta | GA | USA | 33.36996 | -81.9645 | |
14 | AKN | King Salmon | AK | USA | 58.6768 | -156.64922 | |
15 | ALB | Albany | NY | USA | 42.74812 | -73.80298 | |
16 | ALO | Waterloo | IA | USA | 42.55708 | -92.40034 | |
17 | AMA | Amarillo | TX | USA | 35.21937 | -101.70593 | |
18 | ANC | Anchorage | AK | USA | 61.17432 | -149.99619 | |
19 | APN | Alpena | MI | USA | 45.07807 | -83.56029 | |
20 | ASE | Aspen | CO | USA | 39.22316 | -106.86885 | |
21 | ATL | Atlanta | GA | USA | 33.64044 | -84.42694 | |
22 | ATW | Appleton | WI | USA | 44.25741 | -88.51948 | |
23 | AUS | Austin | TX | USA | 30.19453 | -97.66987 | |
24 | AVL | Asheville | NC | USA | 35.43619 | -82.54181 | |
25 | AVP | Wilkes-Barre/Scranton | PA | USA | 41.33815 | -75.72427 | |
26 | AZO | Kalamazoo | MI | USA | 42.23488 | -85.55206 | |
27 | BDL | Windsor Locks | CT | USA | 41.93887 | -72.68323 | |
28 | BET | Bethel | AK | USA | 60.77978 | -161.838 | |
29 | BFL | Bakersfield | CA | USA | 35.4336 | -119.05677 | |
30 | BGM | Binghamton | NY | USA | 42.20848 | -75.97961 | |
31 | BGR | Bangor | ME | USA | 44.80744 | -68.82814 | |
32 | BHM | Birmingham | AL | USA | 33.56294 | -86.75355 | |
33 | BIL | Billings | MT | USA | 45.80766 | -108.54286 | |
34 | BIS | Bismarck | ND | USA | 46.77411 | -100.74672 | |
35 | BJI | Bemidji | MN | USA | 47.50942 | -94.93372 | |
36 | BLI | Bellingham | WA | USA | 48.79275 | -122.53753 | |
37 | BMI | Bloomington | IL | USA | 40.47799 | -88.91595 | |
38 | BNA | Nashville | TN | USA | 36.12448 | -86.67818 | |
39 | BOI | Boise | ID | USA | 43.56444 | -116.22278 | |
40 | BOS | Boston | MA | USA | 42.36435 | -71.00518 | |
41 | BPT | Beaumont/Port Arthur | TX | USA | 29.95083 | -94.02069 | |
42 | BQK | Brunswick | GA | USA | 31.25903 | -81.46631 | |
43 | BQN | Aguadilla | PR | USA | 18.49486 | -67.12944 | |
44 | BRD | Brainerd | MN | USA | 46.39786 | -94.13723 | |
45 | BRO | Brownsville | TX | USA | 25.90683 | -97.42586 | |
46 | BRW | Barrow | AK | USA | 71.28545 | -156.766 | |
47 | BTM | Butte | MT | USA | 45.9548 | -112.49746 | |
48 | BTR | Baton Rouge | LA | USA | 30.53316 | -91.14963 | |
49 | BTV | Burlington | VT | USA | 44.473 | -73.15031 | |
50 | BUF | Buffalo | NY | USA | 42.94052 | -78.73217 | |
51 | BUR | Burbank | CA | USA | 34.20062 | -118.3585 | |
52 | BWI | Baltimore | MD | USA | 39.1754 | -76.6682 | |
53 | BZN | Bozeman | MT | USA | 45.7769 | -111.15301 | |
54 | CAE | Columbia | SC | USA | 33.93884 | -81.11954 | |
55 | CAK | Akron | OH | USA | 40.91631 | -81.44247 | |
56 | CDC | Cedar City | UT | USA | 37.70097 | -113.09858 | |
57 | CDV | Cordova | AK | USA | 60.49183 | -145.47765 | |
58 | CEC | Crescent City | CA | USA | 41.78016 | -124.23653 | |
59 | CHA | Chattanooga | TN | USA | 35.03527 | -85.20379 | |
60 | CHO | Charlottesville | VA | USA | 38.13864 | -78.45286 | |
61 | CHS | Charleston | SC | USA | 32.89865 | -80.04051 | |
62 | CID | Cedar Rapids | IA | USA | 41.88459 | -91.71087 | |
63 | CIU | Sault Ste. Marie | MI | USA | 46.25075 | -84.47239 | |
64 | CLD | San Diego | CA | USA | 33.12723 | -117.27873 | |
65 | CLE | Cleveland | OH | USA | 41.41089 | -81.8494 | |
66 | CLL | College Station | TX | USA | 30.58859 | -96.36382 | |
67 | CLT | Charlotte | NC | USA | 35.21401 | -80.94313 | |
68 | CMH | Columbus | OH | USA | 39.99799 | -82.89188 | |
69 | CMI | Champaign/Urbana | IL | USA | 40.03925 | -88.27806 | |
70 | CMX | Hancock | MI | USA | 47.16842 | -88.48906 | |
71 | CNY | Moab | UT | USA | 38.75496 | -109.75484 | |
72 | COD | Cody | WY | USA | 44.52019 | -109.0238 | |
73 | COS | Colorado Springs | CO | USA | 38.80581 | -104.70025 | |
74 | COU | Columbia | MO | USA | 38.81809 | -92.21963 | |
75 | CPR | Casper | WY | USA | 42.90836 | -106.46447 | |
76 | CRP | Corpus Christi | TX | USA | 27.77036 | -97.50122 | |
77 | CRW | Charleston | WV | USA | 38.37315 | -81.59319 | |
78 | CSG | Columbus | GA | USA | 32.51633 | -84.93886 | |
79 | CVG | Covington | KY | USA | 39.04614 | -84.66217 | |
80 | CWA | Mosinee | WI | USA | 44.77762 | -89.66678 | |
81 | DAB | Daytona Beach | FL | USA | 29.17992 | -81.05806 | |
82 | DAL | Dallas | TX | USA | 32.84711 | -96.85177 | |
83 | DAY | Dayton | OH | USA | 39.90238 | -84.21938 | |
84 | DBQ | Dubuque | IA | USA | 42.40296 | -90.70917 | |
85 | DCA | Arlington | VA | USA | 38.85208 | -77.03772 | |
86 | DEN | Denver | CO | USA | 39.85841 | -104.667 | |
87 | DFW | Dallas-Fort Worth | TX | USA | 32.89595 | -97.0372 | |
88 | DHN | Dothan | AL | USA | 31.32134 | -85.44963 | |
89 | DIK | Dickinson | ND | USA | 46.79739 | -102.80195 | |
90 | DLG | Dillingham | AK | USA | 59.04541 | -158.50334 | |
91 | DLH | Duluth | MN | USA | 46.84209 | -92.19365 | |
92 | DRO | Durango | CO | USA | 37.15152 | -107.75377 | |
93 | DSM | Des Moines | IA | USA | 41.53493 | -93.66068 | |
94 | DTW | Detroit | MI | USA | 42.21206 | -83.34884 | |
95 | DVL | Devils Lake | ND | USA | 48.11425 | -98.90878 | |
96 | EAU | Eau Claire | WI | USA | 44.86526 | -91.48507 | |
97 | ECP | Panama City | FL | USA | [null] | [null] | |
98 | EGE | Eagle | CO | USA | 39.64257 | -106.9177 | |
99 | EKO | Elko | NV | USA | 40.82493 | -115.7917 | |
100 | ELM | Elmira | NY | USA | 42.15991 | -76.89144 |
Flights Data Vertica#
We’ll now read a locally stored CSV file with the flights data and materialize it in Vertica.
[7]:
# Reading a csv file and naming the table flights_vertica
flight_vertica = vp.read_csv('flights.csv',
table_name = "flight_vertica")
flight_vertica
[7]:
123 YEARInt | 123 MONTHInt | 123 DAYInt | 123 DAY_OF_WEEKInt | Abc AIRLINEVarchar(20) | 123 FLIGHT_NUMBERInt | Abc TAIL_NUMBERVarchar(20) | Abc ORIGIN_AIRPORTVarchar(20) | Abc DESTINATION_AIRPORTVarchar(20) | 123 SCHEDULED_DEPARTUREInt | 123 DEPARTURE_TIMEInt | 123 DEPARTURE_DELAYInt | 123 TAXI_OUTInt | 123 WHEELS_OFFInt | 123 SCHEDULED_TIMEInt | 123 ELAPSED_TIMEInt | 123 AIR_TIMEInt | 123 DISTANCEInt | 123 WHEELS_ONInt | 123 TAXI_INInt | 123 SCHEDULED_ARRIVALInt | 123 ARRIVAL_TIMEInt | 123 ARRIVAL_DELAYInt | 123 DIVERTEDInt | 123 CANCELLEDInt | Abc CANCELLATION_REASONVarchar(20) | 123 AIR_SYSTEM_DELAYInt | 123 SECURITY_DELAYInt | 123 AIRLINE_DELAYInt | 123 LATE_AIRCRAFT_DELAYInt | 123 WEATHER_DELAYInt | |
1 | 2015 | 1 | 1 | 4 | AA | 1 | N787AA | JFK | LAX | 900 | 855 | -5 | 17 | 912 | 390 | 402 | 378 | 2475 | 1230 | 7 | 1230 | 1237 | 7 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
2 | 2015 | 1 | 1 | 4 | AA | 2 | N795AA | LAX | JFK | 900 | 856 | -4 | 16 | 912 | 335 | 295 | 271 | 2475 | 1643 | 8 | 1735 | 1651 | -44 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
3 | 2015 | 1 | 1 | 4 | AA | 3 | N798AA | JFK | LAX | 1230 | 1226 | -4 | 19 | 1245 | 380 | 382 | 358 | 2475 | 1543 | 5 | 1550 | 1548 | -2 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
4 | 2015 | 1 | 1 | 4 | AA | 4 | N799AA | LAX | JFK | 1220 | 1214 | -6 | 23 | 1237 | 330 | 319 | 284 | 2475 | 2021 | 12 | 2050 | 2033 | -17 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
5 | 2015 | 1 | 1 | 4 | AA | 5 | N376AA | DFW | HNL | 1305 | 1754 | 289 | 21 | 1815 | 515 | 526 | 499 | 3784 | 2234 | 6 | 1740 | 2240 | 300 | 0 | 0 | [null] | 11 | 0 | 197 | 92 | 0 |
6 | 2015 | 1 | 1 | 4 | AA | 6 | N398AA | OGG | DFW | 1805 | [null] | [null] | [null] | [null] | 425 | [null] | [null] | 3711 | [null] | [null] | 510 | [null] | [null] | 0 | 1 | A | [null] | [null] | [null] | [null] | [null] |
7 | 2015 | 1 | 1 | 4 | AA | 7 | N398AA | DFW | OGG | 1215 | 1513 | 178 | 24 | 1537 | 500 | 517 | 490 | 3711 | 1947 | 3 | 1635 | 1950 | 195 | 0 | 0 | [null] | 17 | 0 | 178 | 0 | 0 |
8 | 2015 | 1 | 1 | 4 | AA | 8 | N368AA | HNL | DFW | 1745 | 1933 | 108 | 15 | 1948 | 445 | 446 | 420 | 3784 | 648 | 11 | 510 | 659 | 109 | 0 | 0 | [null] | 1 | 0 | 0 | 108 | 0 |
9 | 2015 | 1 | 1 | 4 | AA | 9 | N792AA | JFK | LAX | 700 | 649 | -11 | 22 | 711 | 380 | 397 | 368 | 2475 | 1019 | 7 | 1020 | 1026 | 6 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
10 | 2015 | 1 | 1 | 4 | AA | 10 | N796AA | LAX | JFK | 2150 | 2150 | 0 | 14 | 2204 | 309 | 294 | 275 | 2475 | 539 | 5 | 559 | 544 | -15 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
11 | 2015 | 1 | 1 | 4 | AA | 12 | N792AA | LAX | JFK | 1115 | 1114 | -1 | 19 | 1133 | 335 | 321 | 287 | 2475 | 1920 | 15 | 1950 | 1935 | -15 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
12 | 2015 | 1 | 1 | 4 | AA | 14 | N5ERAA | OGG | LAX | 2300 | 2254 | -6 | 12 | 2306 | 305 | 289 | 267 | 2486 | 533 | 10 | 605 | 543 | -22 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
13 | 2015 | 1 | 1 | 4 | AA | 15 | N783AA | JFK | SFO | 800 | 755 | -5 | 57 | 852 | 400 | 453 | 388 | 2586 | 1220 | 8 | 1140 | 1228 | 48 | 0 | 0 | [null] | 48 | 0 | 0 | 0 | 0 |
14 | 2015 | 1 | 1 | 4 | AA | 16 | N783AA | SFO | JFK | 1245 | 1316 | 31 | 18 | 1334 | 330 | 333 | 305 | 2586 | 2139 | 10 | 2115 | 2149 | 34 | 0 | 0 | [null] | 3 | 0 | 0 | 31 | 0 |
15 | 2015 | 1 | 1 | 4 | AA | 17 | N019AA | ATL | MIA | 700 | 656 | -4 | 15 | 711 | 112 | 106 | 82 | 594 | 833 | 9 | 852 | 842 | -10 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
16 | 2015 | 1 | 1 | 4 | AA | 19 | N786AA | JFK | LAX | 1100 | 1055 | -5 | 17 | 1112 | 380 | 378 | 354 | 2475 | 1406 | 7 | 1420 | 1413 | -7 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
17 | 2015 | 1 | 1 | 4 | AA | 20 | N789AA | SFO | JFK | 1500 | 1503 | 3 | 19 | 1522 | 325 | 328 | 301 | 2586 | 2323 | 8 | 2325 | 2331 | 6 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
18 | 2015 | 1 | 1 | 4 | AA | 21 | N795AA | JFK | LAX | 1845 | 1851 | 6 | 29 | 1920 | 385 | 407 | 368 | 2475 | 2228 | 10 | 2210 | 2238 | 28 | 0 | 0 | [null] | 22 | 0 | 6 | 0 | 0 |
19 | 2015 | 1 | 1 | 4 | AA | 22 | N786AA | LAX | JFK | 1515 | 1512 | -3 | 23 | 1535 | 320 | 317 | 277 | 2475 | 2312 | 17 | 2335 | 2329 | -6 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
20 | 2015 | 1 | 1 | 4 | AA | 23 | N371AA | DFW | SFO | 755 | 841 | 46 | 35 | 916 | 230 | 233 | 190 | 1464 | 1026 | 8 | 945 | 1034 | 49 | 0 | 0 | [null] | 3 | 0 | 46 | 0 | 0 |
21 | 2015 | 1 | 1 | 4 | AA | 23 | N371AA | SFO | DFW | 1050 | 1123 | 33 | 28 | 1151 | 210 | 217 | 178 | 1464 | 1649 | 11 | 1620 | 1700 | 40 | 0 | 0 | [null] | 7 | 0 | 0 | 33 | 0 |
22 | 2015 | 1 | 1 | 4 | AA | 24 | N784AA | SFO | JFK | 720 | 714 | -6 | 20 | 734 | 340 | 332 | 307 | 2586 | 1541 | 5 | 1600 | 1546 | -14 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
23 | 2015 | 1 | 1 | 4 | AA | 25 | N3AVAA | BOS | LAX | 900 | 857 | -3 | 15 | 912 | 395 | 405 | 381 | 2611 | 1233 | 9 | 1235 | 1242 | 7 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
24 | 2015 | 1 | 1 | 4 | AA | 27 | N5DUAA | DFW | LAS | 1850 | 1929 | 39 | 16 | 1945 | 175 | 165 | 141 | 1055 | 2006 | 8 | 1945 | 2014 | 29 | 0 | 0 | [null] | 0 | 0 | 29 | 0 | 0 |
25 | 2015 | 1 | 1 | 4 | AA | 28 | N358AA | LAX | MIA | 1500 | 1456 | -4 | 19 | 1515 | 290 | 305 | 284 | 2342 | 2259 | 2 | 2250 | 2301 | 11 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
26 | 2015 | 1 | 1 | 4 | AA | 29 | N455AA | ONT | DFW | 1225 | 1356 | 91 | 25 | 1421 | 175 | 217 | 149 | 1188 | 1850 | 43 | 1720 | 1933 | 133 | 0 | 0 | [null] | 42 | 0 | 91 | 0 | 0 |
27 | 2015 | 1 | 1 | 4 | AA | 29 | N568AA | DFW | ONT | 1035 | [null] | [null] | [null] | [null] | 190 | [null] | [null] | 1188 | [null] | [null] | 1145 | [null] | [null] | 0 | 1 | B | [null] | [null] | [null] | [null] | [null] |
28 | 2015 | 1 | 1 | 4 | AA | 30 | N795AA | LAX | JFK | 2330 | 2326 | -4 | 23 | 2349 | 320 | 299 | 270 | 2475 | 719 | 6 | 750 | 725 | -25 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
29 | 2015 | 1 | 1 | 4 | AA | 31 | N5ESAA | LAX | HNL | 805 | 828 | 23 | 12 | 840 | 365 | 362 | 346 | 2556 | 1226 | 4 | 1210 | 1230 | 20 | 0 | 0 | [null] | 0 | 0 | 20 | 0 | 0 |
30 | 2015 | 1 | 1 | 4 | AA | 32 | N794AA | LAX | JFK | 1415 | 1424 | 9 | 14 | 1438 | 325 | 315 | 288 | 2475 | 2226 | 13 | 2240 | 2239 | -1 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
31 | 2015 | 1 | 1 | 4 | AA | 35 | N3ALAA | DFW | HDN | 1110 | 1109 | -1 | 11 | 1120 | 145 | 178 | 111 | 769 | 1211 | 56 | 1235 | 1307 | 32 | 0 | 0 | [null] | 32 | 0 | 0 | 0 | 0 |
32 | 2015 | 1 | 1 | 4 | AA | 35 | N3ALAA | HDN | DFW | 1320 | 1400 | 40 | 11 | 1411 | 135 | 130 | 109 | 769 | 1700 | 10 | 1635 | 1710 | 35 | 0 | 0 | [null] | 0 | 0 | 7 | 28 | 0 |
33 | 2015 | 1 | 1 | 4 | AA | 36 | N3EKAA | SAN | DFW | 935 | 928 | -7 | 13 | 941 | 170 | 172 | 140 | 1171 | 1401 | 19 | 1425 | 1420 | -5 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
34 | 2015 | 1 | 1 | 4 | AA | 37 | N483AA | DFW | SAN | 2100 | 2103 | 3 | 18 | 2121 | 185 | 187 | 166 | 1171 | 2207 | 3 | 2205 | 2210 | 5 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
35 | 2015 | 1 | 1 | 4 | AA | 40 | N3BYAA | DCA | ORD | 955 | 955 | 0 | 9 | 1004 | 130 | 126 | 111 | 612 | 1055 | 6 | 1105 | 1101 | -4 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
36 | 2015 | 1 | 1 | 4 | AA | 41 | N3KEAA | ORD | SEA | 2055 | 2054 | -1 | 16 | 2110 | 270 | 293 | 236 | 1721 | 2306 | 41 | 2325 | 2347 | 22 | 0 | 0 | [null] | 22 | 0 | 0 | 0 | 0 |
37 | 2015 | 1 | 1 | 4 | AA | 42 | N3GPAA | SEA | ORD | 850 | 849 | -1 | 16 | 905 | 235 | 224 | 201 | 1721 | 1426 | 7 | 1445 | 1433 | -12 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
38 | 2015 | 1 | 1 | 4 | AA | 43 | N3BNAA | DFW | DTW | 1030 | [null] | [null] | [null] | [null] | 150 | [null] | [null] | 986 | [null] | [null] | 1400 | [null] | [null] | 0 | 1 | B | [null] | [null] | [null] | [null] | [null] |
39 | 2015 | 1 | 1 | 4 | AA | 43 | N3KCAA | DTW | DFW | 1445 | 1445 | 0 | 19 | 1504 | 180 | 199 | 163 | 986 | 1647 | 17 | 1645 | 1704 | 19 | 0 | 0 | [null] | 19 | 0 | 0 | 0 | 0 |
40 | 2015 | 1 | 1 | 4 | AA | 44 | N3AJAA | LAS | JFK | 810 | 803 | -7 | 17 | 820 | 295 | 272 | 248 | 2248 | 1528 | 7 | 1605 | 1535 | -30 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
41 | 2015 | 1 | 1 | 4 | AA | 45 | N3ELAA | JFK | LAS | 1729 | 1727 | -2 | 21 | 1748 | 355 | 347 | 318 | 2248 | 2006 | 8 | 2024 | 2014 | -10 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
42 | 2015 | 1 | 1 | 4 | AA | 46 | N3CSAA | SJC | ORD | 725 | 725 | 0 | 13 | 738 | 255 | 253 | 226 | 1829 | 1324 | 14 | 1340 | 1338 | -2 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
43 | 2015 | 1 | 1 | 4 | AA | 47 | N3KSAA | ORD | SJC | 1915 | 2035 | 80 | 18 | 2053 | 280 | 261 | 238 | 1829 | 2251 | 5 | 2155 | 2256 | 61 | 0 | 0 | [null] | 0 | 0 | 0 | 61 | 0 |
44 | 2015 | 1 | 1 | 4 | AA | 48 | N3JXAA | SAN | DFW | 835 | 830 | -5 | 13 | 843 | 175 | 164 | 141 | 1171 | 1304 | 10 | 1330 | 1314 | -16 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
45 | 2015 | 1 | 1 | 4 | AA | 49 | N5ELAA | DFW | SAN | 1935 | 2006 | 31 | 15 | 2021 | 185 | 178 | 160 | 1171 | 2101 | 3 | 2040 | 2104 | 24 | 0 | 0 | [null] | 0 | 0 | 24 | 0 | 0 |
46 | 2015 | 1 | 1 | 4 | AA | 50 | N570AA | SLC | DFW | 815 | 824 | 9 | 36 | 900 | 165 | 179 | 134 | 989 | 1214 | 9 | 1200 | 1223 | 23 | 0 | 0 | [null] | 14 | 0 | 9 | 0 | 0 |
47 | 2015 | 1 | 1 | 4 | AA | 51 | N471AA | DFW | SLC | 1725 | 2035 | 190 | 24 | 2059 | 170 | 161 | 130 | 989 | 2209 | 7 | 1915 | 2216 | 181 | 0 | 0 | [null] | 0 | 0 | 22 | 159 | 0 |
48 | 2015 | 1 | 1 | 4 | AA | 52 | N3LMAA | LAX | DCA | 1225 | 1222 | -3 | 18 | 1240 | 290 | 279 | 253 | 2311 | 1953 | 8 | 2015 | 2001 | -14 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
49 | 2015 | 1 | 1 | 4 | AA | 53 | N3LHAA | DCA | LAX | 840 | 838 | -2 | 12 | 850 | 360 | 350 | 331 | 2311 | 1121 | 7 | 1140 | 1128 | -12 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
50 | 2015 | 1 | 1 | 4 | AA | 54 | N3CWAA | LAS | ORD | 910 | 906 | -4 | 16 | 922 | 215 | 193 | 170 | 1514 | 1412 | 7 | 1445 | 1419 | -26 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
51 | 2015 | 1 | 1 | 4 | AA | 55 | N3JJAA | ORD | LAS | 1820 | 1924 | 64 | 13 | 1937 | 230 | 225 | 205 | 1514 | 2102 | 7 | 2010 | 2109 | 59 | 0 | 0 | [null] | 0 | 0 | 59 | 0 | 0 |
52 | 2015 | 1 | 1 | 4 | AA | 58 | N5DFAA | KOA | LAX | 1400 | 1400 | 0 | 22 | 1422 | 310 | 320 | 276 | 2504 | 2058 | 22 | 2110 | 2120 | 10 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
53 | 2015 | 1 | 1 | 4 | AA | 59 | N5DFAA | LAX | KOA | 840 | 837 | -3 | 17 | 854 | 350 | 352 | 332 | 2504 | 1226 | 3 | 1230 | 1229 | -1 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
54 | 2015 | 1 | 1 | 4 | AA | 60 | N372AA | DFW | MIA | 1754 | 1955 | 121 | 15 | 2010 | 160 | 151 | 133 | 1121 | 2323 | 3 | 2134 | 2326 | 112 | 0 | 0 | [null] | 0 | 0 | 112 | 0 | 0 |
55 | 2015 | 1 | 1 | 4 | AA | 61 | N850AA | MIA | DFW | 715 | 719 | 4 | 13 | 732 | 199 | 192 | 167 | 1121 | 919 | 12 | 934 | 931 | -3 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
56 | 2015 | 1 | 1 | 4 | AA | 63 | N3ECAA | MIA | PHX | 1950 | 1955 | 5 | 23 | 2018 | 317 | 302 | 271 | 1972 | 2249 | 8 | 2307 | 2257 | -10 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
57 | 2015 | 1 | 1 | 4 | AA | 64 | N3HXAA | DFW | JFK | 1025 | 1041 | 16 | 13 | 1054 | 200 | 187 | 169 | 1391 | 1443 | 5 | 1445 | 1448 | 3 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
58 | 2015 | 1 | 1 | 4 | AA | 65 | N3HLAA | JFK | DFW | 1615 | 1625 | 10 | 19 | 1644 | 255 | 294 | 227 | 1391 | 1931 | 48 | 1930 | 2019 | 49 | 0 | 0 | [null] | 39 | 0 | 10 | 0 | 0 |
59 | 2015 | 1 | 1 | 4 | AA | 66 | N631AA | SJU | JFK | 1151 | 1142 | -9 | 22 | 1204 | 245 | 247 | 221 | 1598 | 1445 | 4 | 1456 | 1449 | -7 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
60 | 2015 | 1 | 1 | 4 | AA | 67 | N631AA | JFK | SJU | 1605 | 1629 | 24 | 23 | 1652 | 235 | 211 | 182 | 1598 | 2054 | 6 | 2100 | 2100 | 0 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
61 | 2015 | 1 | 1 | 4 | AA | 68 | N5EGAA | SFO | MIA | 700 | 703 | 3 | 14 | 717 | 324 | 317 | 295 | 2585 | 1512 | 8 | 1524 | 1520 | -4 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
62 | 2015 | 1 | 1 | 4 | AA | 69 | N3HTAA | MIA | SFO | 1835 | 1836 | 1 | 12 | 1848 | 386 | 359 | 342 | 2585 | 2130 | 5 | 2201 | 2135 | -26 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
63 | 2015 | 1 | 1 | 4 | AA | 70 | N5BTAA | SAN | DFW | 705 | 659 | -6 | 22 | 721 | 180 | 171 | 137 | 1171 | 1138 | 12 | 1205 | 1150 | -15 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
64 | 2015 | 1 | 1 | 4 | AA | 71 | N3GHAA | DFW | SAN | 1805 | 1803 | -2 | 14 | 1817 | 180 | 176 | 160 | 1171 | 1857 | 2 | 1905 | 1859 | -6 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
65 | 2015 | 1 | 1 | 4 | AA | 72 | N5EKAA | DFW | MCO | 600 | 606 | 6 | 18 | 624 | 145 | 142 | 120 | 985 | 924 | 4 | 925 | 928 | 3 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
66 | 2015 | 1 | 1 | 4 | AA | 72 | N5EKAA | MCO | DFW | 1020 | 1015 | -5 | 16 | 1031 | 175 | 171 | 143 | 985 | 1154 | 12 | 1215 | 1206 | -9 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
67 | 2015 | 1 | 1 | 4 | AA | 73 | N3FBAA | PHL | CLT | 1005 | 957 | -8 | 26 | 1023 | 120 | 108 | 76 | 449 | 1139 | 6 | 1205 | 1145 | -20 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
68 | 2015 | 1 | 1 | 4 | AA | 74 | N488AA | CLE | DFW | 1555 | 1552 | -3 | 11 | 1603 | 190 | 181 | 161 | 1021 | 1744 | 9 | 1805 | 1753 | -12 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
69 | 2015 | 1 | 1 | 4 | AA | 74 | N505AA | DFW | CLE | 1145 | [null] | [null] | [null] | [null] | 150 | [null] | [null] | 1021 | [null] | [null] | 1515 | [null] | [null] | 0 | 1 | B | [null] | [null] | [null] | [null] | [null] |
70 | 2015 | 1 | 1 | 4 | AA | 75 | N3HPAA | IAD | LAX | 1830 | 1822 | -8 | 17 | 1839 | 360 | 358 | 335 | 2288 | 2114 | 6 | 2130 | 2120 | -10 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
71 | 2015 | 1 | 1 | 4 | AA | 76 | N3HPAA | LAX | IAD | 950 | 946 | -4 | 18 | 1004 | 285 | 265 | 243 | 2288 | 1707 | 4 | 1735 | 1711 | -24 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
72 | 2015 | 1 | 1 | 4 | AA | 76 | N3HPAA | SFO | LAX | 730 | 723 | -7 | 15 | 738 | 85 | 97 | 49 | 337 | 827 | 33 | 855 | 900 | 5 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
73 | 2015 | 1 | 1 | 4 | AA | 79 | N520AA | DFW | SMF | 2140 | 2219 | 39 | 30 | 2249 | 225 | 224 | 188 | 1431 | 2357 | 6 | 2325 | 3 | 38 | 0 | 0 | [null] | 0 | 0 | 0 | 38 | 0 |
74 | 2015 | 1 | 1 | 4 | AA | 80 | N4XTAA | TUS | DFW | 1655 | 1741 | 46 | 10 | 1751 | 130 | 130 | 105 | 813 | 2036 | 15 | 2005 | 2051 | 46 | 0 | 0 | [null] | 0 | 0 | 0 | 46 | 0 |
75 | 2015 | 1 | 1 | 4 | AA | 81 | N4XCAA | DFW | TUS | 2245 | 2308 | 23 | 13 | 2321 | 140 | 140 | 123 | 813 | 24 | 4 | 5 | 28 | 23 | 0 | 0 | [null] | 0 | 0 | 0 | 23 | 0 |
76 | 2015 | 1 | 1 | 4 | AA | 83 | N4XBAA | MCO | DFW | 735 | 733 | -2 | 16 | 749 | 180 | 174 | 146 | 985 | 915 | 12 | 935 | 927 | -8 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
77 | 2015 | 1 | 1 | 4 | AA | 84 | N3AXAA | BOS | JFK | 940 | 930 | -10 | 15 | 945 | 75 | 69 | 46 | 187 | 1031 | 8 | 1055 | 1039 | -16 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
78 | 2015 | 1 | 1 | 4 | AA | 84 | N3AXAA | JFK | BOS | 730 | 734 | 4 | 17 | 751 | 70 | 65 | 44 | 187 | 835 | 4 | 840 | 839 | -1 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
79 | 2015 | 1 | 1 | 4 | AA | 85 | N785AA | JFK | SFO | 1530 | 1524 | -6 | 20 | 1544 | 400 | 386 | 361 | 2586 | 1845 | 5 | 1910 | 1850 | -20 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
80 | 2015 | 1 | 1 | 4 | AA | 86 | N3GTAA | PDX | ORD | 705 | 701 | -4 | 15 | 716 | 235 | 230 | 208 | 1739 | 1244 | 7 | 1300 | 1251 | -9 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
81 | 2015 | 1 | 1 | 4 | AA | 87 | N490AA | ORD | RNO | 1845 | 1840 | -5 | 11 | 1851 | 265 | 228 | 213 | 1671 | 2024 | 4 | 2110 | 2028 | -42 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
82 | 2015 | 1 | 1 | 4 | AA | 89 | N3KVAA | IAH | MIA | 520 | 618 | 58 | 19 | 637 | 141 | 137 | 111 | 964 | 928 | 7 | 841 | 935 | 54 | 0 | 0 | [null] | 0 | 0 | 54 | 0 | 0 |
83 | 2015 | 1 | 1 | 4 | AA | 92 | N3KHAA | SAN | ORD | 900 | 900 | 0 | 15 | 915 | 240 | 210 | 189 | 1723 | 1424 | 6 | 1500 | 1430 | -30 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
84 | 2015 | 1 | 1 | 4 | AA | 93 | N3HHAA | ORD | SAN | 1720 | 1805 | 45 | 10 | 1815 | 255 | 222 | 210 | 1723 | 1945 | 2 | 1935 | 1947 | 12 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
85 | 2015 | 1 | 1 | 4 | AA | 94 | N3ANAA | SAN | JFK | 755 | 810 | 15 | 13 | 823 | 320 | 281 | 262 | 2446 | 1545 | 6 | 1615 | 1551 | -24 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
86 | 2015 | 1 | 1 | 4 | AA | 95 | N3ANAA | JFK | SAN | 1720 | 1749 | 29 | 18 | 1807 | 380 | 386 | 363 | 2446 | 2110 | 5 | 2040 | 2115 | 35 | 0 | 0 | [null] | 6 | 0 | 29 | 0 | 0 |
87 | 2015 | 1 | 1 | 4 | AA | 96 | N553AA | DFW | STL | 820 | 831 | 11 | 21 | 852 | 95 | 94 | 70 | 550 | 1002 | 3 | 955 | 1005 | 10 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
88 | 2015 | 1 | 1 | 4 | AA | 96 | N553AA | STL | DFW | 1035 | 1051 | 16 | 15 | 1106 | 115 | 131 | 98 | 550 | 1244 | 18 | 1230 | 1302 | 32 | 0 | 0 | [null] | 16 | 0 | 6 | 10 | 0 |
89 | 2015 | 1 | 1 | 4 | AA | 97 | N3ENAA | DCA | DFW | 845 | 842 | -3 | 14 | 856 | 215 | 210 | 185 | 1192 | 1101 | 11 | 1120 | 1112 | -8 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
90 | 2015 | 1 | 1 | 4 | AA | 99 | N3KMAA | ORD | PDX | 1550 | 1553 | 3 | 11 | 1604 | 275 | 253 | 239 | 1739 | 1803 | 3 | 1825 | 1806 | -19 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
91 | 2015 | 1 | 1 | 4 | AA | 102 | N376AA | HNL | DFW | 1920 | 2356 | 276 | 17 | 13 | 445 | 445 | 417 | 3784 | 1110 | 11 | 645 | 1121 | 276 | 0 | 0 | [null] | 0 | 0 | 0 | 276 | 0 |
92 | 2015 | 1 | 1 | 4 | AA | 103 | N4XSAA | DFW | MSY | 1410 | 1433 | 23 | 13 | 1446 | 80 | 80 | 62 | 447 | 1548 | 5 | 1530 | 1553 | 23 | 0 | 0 | [null] | 0 | 0 | 23 | 0 | 0 |
93 | 2015 | 1 | 1 | 4 | AA | 103 | N4XSAA | MSY | DFW | 1610 | 1624 | 14 | 8 | 1632 | 100 | 91 | 74 | 447 | 1746 | 9 | 1750 | 1755 | 5 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
94 | 2015 | 1 | 1 | 4 | AA | 110 | N3LRAA | SNA | ORD | 805 | 801 | -4 | 7 | 808 | 230 | 211 | 194 | 1726 | 1322 | 10 | 1355 | 1332 | -23 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
95 | 2015 | 1 | 1 | 4 | AA | 111 | N3LYAA | ORD | SNA | 1720 | 1719 | -1 | 11 | 1730 | 260 | 237 | 222 | 1726 | 1912 | 4 | 1940 | 1916 | -24 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
96 | 2015 | 1 | 1 | 4 | AA | 112 | N857AA | DEN | MIA | 1015 | 1017 | 2 | 19 | 1036 | 224 | 229 | 199 | 1709 | 1555 | 11 | 1559 | 1606 | 7 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
97 | 2015 | 1 | 1 | 4 | AA | 113 | N3GRAA | MIA | DEN | 2115 | 2115 | 0 | 21 | 2136 | 269 | 269 | 235 | 1709 | 2331 | 13 | 2344 | 2344 | 0 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
98 | 2015 | 1 | 1 | 4 | AA | 115 | N3CTAA | LAX | MIA | 105 | 103 | -2 | 14 | 117 | 286 | 276 | 255 | 2342 | 832 | 7 | 851 | 839 | -12 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
99 | 2015 | 1 | 1 | 4 | AA | 116 | N383AA | OGG | DFW | 1955 | 2016 | 21 | 10 | 2026 | 430 | 433 | 418 | 3711 | 724 | 5 | 705 | 729 | 24 | 0 | 0 | [null] | 3 | 0 | 21 | 0 | 0 |
100 | 2015 | 1 | 1 | 4 | AA | 117 | N793AA | JFK | LAX | 1445 | 1438 | -7 | 28 | 1506 | 390 | 389 | 355 | 2475 | 1801 | 6 | 1815 | 1807 | -8 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
[8]:
%%sql
/* Fetch all the data from the table flight_vertica. */
SELECT * FROM flight_vertica;
[8]:
123 YEARInteger | 123 MONTHInteger | 123 DAYInteger | 123 DAY_OF_WEEKInteger | Abc AIRLINEVarchar(20) | 123 FLIGHT_NUMBERInteger | Abc TAIL_NUMBERVarchar(20) | Abc ORIGIN_AIRPORTVarchar(20) | Abc DESTINATION_AIRPORTVarchar(20) | 123 SCHEDULED_DEPARTUREInteger | 123 DEPARTURE_TIMEInteger | 123 DEPARTURE_DELAYInteger | 123 TAXI_OUTInteger | 123 WHEELS_OFFInteger | 123 SCHEDULED_TIMEInteger | 123 ELAPSED_TIMEInteger | 123 AIR_TIMEInteger | 123 DISTANCEInteger | 123 WHEELS_ONInteger | 123 TAXI_INInteger | 123 SCHEDULED_ARRIVALInteger | 123 ARRIVAL_TIMEInteger | 123 ARRIVAL_DELAYInteger | 123 DIVERTEDInteger | 123 CANCELLEDInteger | Abc CANCELLATION_REASONVarchar(20) | 123 AIR_SYSTEM_DELAYInteger | 123 SECURITY_DELAYInteger | 123 AIRLINE_DELAYInteger | 123 LATE_AIRCRAFT_DELAYInteger | 123 WEATHER_DELAYInteger | |
1 | 2015 | 1 | 1 | 4 | AA | 1 | N787AA | JFK | LAX | 900 | 855 | -5 | 17 | 912 | 390 | 402 | 378 | 2475 | 1230 | 7 | 1230 | 1237 | 7 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
2 | 2015 | 1 | 1 | 4 | AA | 2 | N795AA | LAX | JFK | 900 | 856 | -4 | 16 | 912 | 335 | 295 | 271 | 2475 | 1643 | 8 | 1735 | 1651 | -44 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
3 | 2015 | 1 | 1 | 4 | AA | 3 | N798AA | JFK | LAX | 1230 | 1226 | -4 | 19 | 1245 | 380 | 382 | 358 | 2475 | 1543 | 5 | 1550 | 1548 | -2 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
4 | 2015 | 1 | 1 | 4 | AA | 4 | N799AA | LAX | JFK | 1220 | 1214 | -6 | 23 | 1237 | 330 | 319 | 284 | 2475 | 2021 | 12 | 2050 | 2033 | -17 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
5 | 2015 | 1 | 1 | 4 | AA | 5 | N376AA | DFW | HNL | 1305 | 1754 | 289 | 21 | 1815 | 515 | 526 | 499 | 3784 | 2234 | 6 | 1740 | 2240 | 300 | 0 | 0 | [null] | 11 | 0 | 197 | 92 | 0 |
6 | 2015 | 1 | 1 | 4 | AA | 6 | N398AA | OGG | DFW | 1805 | [null] | [null] | [null] | [null] | 425 | [null] | [null] | 3711 | [null] | [null] | 510 | [null] | [null] | 0 | 1 | A | [null] | [null] | [null] | [null] | [null] |
7 | 2015 | 1 | 1 | 4 | AA | 7 | N398AA | DFW | OGG | 1215 | 1513 | 178 | 24 | 1537 | 500 | 517 | 490 | 3711 | 1947 | 3 | 1635 | 1950 | 195 | 0 | 0 | [null] | 17 | 0 | 178 | 0 | 0 |
8 | 2015 | 1 | 1 | 4 | AA | 8 | N368AA | HNL | DFW | 1745 | 1933 | 108 | 15 | 1948 | 445 | 446 | 420 | 3784 | 648 | 11 | 510 | 659 | 109 | 0 | 0 | [null] | 1 | 0 | 0 | 108 | 0 |
9 | 2015 | 1 | 1 | 4 | AA | 9 | N792AA | JFK | LAX | 700 | 649 | -11 | 22 | 711 | 380 | 397 | 368 | 2475 | 1019 | 7 | 1020 | 1026 | 6 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
10 | 2015 | 1 | 1 | 4 | AA | 10 | N796AA | LAX | JFK | 2150 | 2150 | 0 | 14 | 2204 | 309 | 294 | 275 | 2475 | 539 | 5 | 559 | 544 | -15 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
11 | 2015 | 1 | 1 | 4 | AA | 12 | N792AA | LAX | JFK | 1115 | 1114 | -1 | 19 | 1133 | 335 | 321 | 287 | 2475 | 1920 | 15 | 1950 | 1935 | -15 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
12 | 2015 | 1 | 1 | 4 | AA | 14 | N5ERAA | OGG | LAX | 2300 | 2254 | -6 | 12 | 2306 | 305 | 289 | 267 | 2486 | 533 | 10 | 605 | 543 | -22 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
13 | 2015 | 1 | 1 | 4 | AA | 15 | N783AA | JFK | SFO | 800 | 755 | -5 | 57 | 852 | 400 | 453 | 388 | 2586 | 1220 | 8 | 1140 | 1228 | 48 | 0 | 0 | [null] | 48 | 0 | 0 | 0 | 0 |
14 | 2015 | 1 | 1 | 4 | AA | 16 | N783AA | SFO | JFK | 1245 | 1316 | 31 | 18 | 1334 | 330 | 333 | 305 | 2586 | 2139 | 10 | 2115 | 2149 | 34 | 0 | 0 | [null] | 3 | 0 | 0 | 31 | 0 |
15 | 2015 | 1 | 1 | 4 | AA | 17 | N019AA | ATL | MIA | 700 | 656 | -4 | 15 | 711 | 112 | 106 | 82 | 594 | 833 | 9 | 852 | 842 | -10 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
16 | 2015 | 1 | 1 | 4 | AA | 19 | N786AA | JFK | LAX | 1100 | 1055 | -5 | 17 | 1112 | 380 | 378 | 354 | 2475 | 1406 | 7 | 1420 | 1413 | -7 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
17 | 2015 | 1 | 1 | 4 | AA | 20 | N789AA | SFO | JFK | 1500 | 1503 | 3 | 19 | 1522 | 325 | 328 | 301 | 2586 | 2323 | 8 | 2325 | 2331 | 6 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
18 | 2015 | 1 | 1 | 4 | AA | 21 | N795AA | JFK | LAX | 1845 | 1851 | 6 | 29 | 1920 | 385 | 407 | 368 | 2475 | 2228 | 10 | 2210 | 2238 | 28 | 0 | 0 | [null] | 22 | 0 | 6 | 0 | 0 |
19 | 2015 | 1 | 1 | 4 | AA | 22 | N786AA | LAX | JFK | 1515 | 1512 | -3 | 23 | 1535 | 320 | 317 | 277 | 2475 | 2312 | 17 | 2335 | 2329 | -6 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
20 | 2015 | 1 | 1 | 4 | AA | 23 | N371AA | DFW | SFO | 755 | 841 | 46 | 35 | 916 | 230 | 233 | 190 | 1464 | 1026 | 8 | 945 | 1034 | 49 | 0 | 0 | [null] | 3 | 0 | 46 | 0 | 0 |
21 | 2015 | 1 | 1 | 4 | AA | 23 | N371AA | SFO | DFW | 1050 | 1123 | 33 | 28 | 1151 | 210 | 217 | 178 | 1464 | 1649 | 11 | 1620 | 1700 | 40 | 0 | 0 | [null] | 7 | 0 | 0 | 33 | 0 |
22 | 2015 | 1 | 1 | 4 | AA | 24 | N784AA | SFO | JFK | 720 | 714 | -6 | 20 | 734 | 340 | 332 | 307 | 2586 | 1541 | 5 | 1600 | 1546 | -14 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
23 | 2015 | 1 | 1 | 4 | AA | 25 | N3AVAA | BOS | LAX | 900 | 857 | -3 | 15 | 912 | 395 | 405 | 381 | 2611 | 1233 | 9 | 1235 | 1242 | 7 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
24 | 2015 | 1 | 1 | 4 | AA | 27 | N5DUAA | DFW | LAS | 1850 | 1929 | 39 | 16 | 1945 | 175 | 165 | 141 | 1055 | 2006 | 8 | 1945 | 2014 | 29 | 0 | 0 | [null] | 0 | 0 | 29 | 0 | 0 |
25 | 2015 | 1 | 1 | 4 | AA | 28 | N358AA | LAX | MIA | 1500 | 1456 | -4 | 19 | 1515 | 290 | 305 | 284 | 2342 | 2259 | 2 | 2250 | 2301 | 11 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
26 | 2015 | 1 | 1 | 4 | AA | 29 | N455AA | ONT | DFW | 1225 | 1356 | 91 | 25 | 1421 | 175 | 217 | 149 | 1188 | 1850 | 43 | 1720 | 1933 | 133 | 0 | 0 | [null] | 42 | 0 | 91 | 0 | 0 |
27 | 2015 | 1 | 1 | 4 | AA | 29 | N568AA | DFW | ONT | 1035 | [null] | [null] | [null] | [null] | 190 | [null] | [null] | 1188 | [null] | [null] | 1145 | [null] | [null] | 0 | 1 | B | [null] | [null] | [null] | [null] | [null] |
28 | 2015 | 1 | 1 | 4 | AA | 30 | N795AA | LAX | JFK | 2330 | 2326 | -4 | 23 | 2349 | 320 | 299 | 270 | 2475 | 719 | 6 | 750 | 725 | -25 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
29 | 2015 | 1 | 1 | 4 | AA | 31 | N5ESAA | LAX | HNL | 805 | 828 | 23 | 12 | 840 | 365 | 362 | 346 | 2556 | 1226 | 4 | 1210 | 1230 | 20 | 0 | 0 | [null] | 0 | 0 | 20 | 0 | 0 |
30 | 2015 | 1 | 1 | 4 | AA | 32 | N794AA | LAX | JFK | 1415 | 1424 | 9 | 14 | 1438 | 325 | 315 | 288 | 2475 | 2226 | 13 | 2240 | 2239 | -1 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
31 | 2015 | 1 | 1 | 4 | AA | 35 | N3ALAA | DFW | HDN | 1110 | 1109 | -1 | 11 | 1120 | 145 | 178 | 111 | 769 | 1211 | 56 | 1235 | 1307 | 32 | 0 | 0 | [null] | 32 | 0 | 0 | 0 | 0 |
32 | 2015 | 1 | 1 | 4 | AA | 35 | N3ALAA | HDN | DFW | 1320 | 1400 | 40 | 11 | 1411 | 135 | 130 | 109 | 769 | 1700 | 10 | 1635 | 1710 | 35 | 0 | 0 | [null] | 0 | 0 | 7 | 28 | 0 |
33 | 2015 | 1 | 1 | 4 | AA | 36 | N3EKAA | SAN | DFW | 935 | 928 | -7 | 13 | 941 | 170 | 172 | 140 | 1171 | 1401 | 19 | 1425 | 1420 | -5 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
34 | 2015 | 1 | 1 | 4 | AA | 37 | N483AA | DFW | SAN | 2100 | 2103 | 3 | 18 | 2121 | 185 | 187 | 166 | 1171 | 2207 | 3 | 2205 | 2210 | 5 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
35 | 2015 | 1 | 1 | 4 | AA | 40 | N3BYAA | DCA | ORD | 955 | 955 | 0 | 9 | 1004 | 130 | 126 | 111 | 612 | 1055 | 6 | 1105 | 1101 | -4 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
36 | 2015 | 1 | 1 | 4 | AA | 41 | N3KEAA | ORD | SEA | 2055 | 2054 | -1 | 16 | 2110 | 270 | 293 | 236 | 1721 | 2306 | 41 | 2325 | 2347 | 22 | 0 | 0 | [null] | 22 | 0 | 0 | 0 | 0 |
37 | 2015 | 1 | 1 | 4 | AA | 42 | N3GPAA | SEA | ORD | 850 | 849 | -1 | 16 | 905 | 235 | 224 | 201 | 1721 | 1426 | 7 | 1445 | 1433 | -12 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
38 | 2015 | 1 | 1 | 4 | AA | 43 | N3BNAA | DFW | DTW | 1030 | [null] | [null] | [null] | [null] | 150 | [null] | [null] | 986 | [null] | [null] | 1400 | [null] | [null] | 0 | 1 | B | [null] | [null] | [null] | [null] | [null] |
39 | 2015 | 1 | 1 | 4 | AA | 43 | N3KCAA | DTW | DFW | 1445 | 1445 | 0 | 19 | 1504 | 180 | 199 | 163 | 986 | 1647 | 17 | 1645 | 1704 | 19 | 0 | 0 | [null] | 19 | 0 | 0 | 0 | 0 |
40 | 2015 | 1 | 1 | 4 | AA | 44 | N3AJAA | LAS | JFK | 810 | 803 | -7 | 17 | 820 | 295 | 272 | 248 | 2248 | 1528 | 7 | 1605 | 1535 | -30 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
41 | 2015 | 1 | 1 | 4 | AA | 45 | N3ELAA | JFK | LAS | 1729 | 1727 | -2 | 21 | 1748 | 355 | 347 | 318 | 2248 | 2006 | 8 | 2024 | 2014 | -10 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
42 | 2015 | 1 | 1 | 4 | AA | 46 | N3CSAA | SJC | ORD | 725 | 725 | 0 | 13 | 738 | 255 | 253 | 226 | 1829 | 1324 | 14 | 1340 | 1338 | -2 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
43 | 2015 | 1 | 1 | 4 | AA | 47 | N3KSAA | ORD | SJC | 1915 | 2035 | 80 | 18 | 2053 | 280 | 261 | 238 | 1829 | 2251 | 5 | 2155 | 2256 | 61 | 0 | 0 | [null] | 0 | 0 | 0 | 61 | 0 |
44 | 2015 | 1 | 1 | 4 | AA | 48 | N3JXAA | SAN | DFW | 835 | 830 | -5 | 13 | 843 | 175 | 164 | 141 | 1171 | 1304 | 10 | 1330 | 1314 | -16 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
45 | 2015 | 1 | 1 | 4 | AA | 49 | N5ELAA | DFW | SAN | 1935 | 2006 | 31 | 15 | 2021 | 185 | 178 | 160 | 1171 | 2101 | 3 | 2040 | 2104 | 24 | 0 | 0 | [null] | 0 | 0 | 24 | 0 | 0 |
46 | 2015 | 1 | 1 | 4 | AA | 50 | N570AA | SLC | DFW | 815 | 824 | 9 | 36 | 900 | 165 | 179 | 134 | 989 | 1214 | 9 | 1200 | 1223 | 23 | 0 | 0 | [null] | 14 | 0 | 9 | 0 | 0 |
47 | 2015 | 1 | 1 | 4 | AA | 51 | N471AA | DFW | SLC | 1725 | 2035 | 190 | 24 | 2059 | 170 | 161 | 130 | 989 | 2209 | 7 | 1915 | 2216 | 181 | 0 | 0 | [null] | 0 | 0 | 22 | 159 | 0 |
48 | 2015 | 1 | 1 | 4 | AA | 52 | N3LMAA | LAX | DCA | 1225 | 1222 | -3 | 18 | 1240 | 290 | 279 | 253 | 2311 | 1953 | 8 | 2015 | 2001 | -14 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
49 | 2015 | 1 | 1 | 4 | AA | 53 | N3LHAA | DCA | LAX | 840 | 838 | -2 | 12 | 850 | 360 | 350 | 331 | 2311 | 1121 | 7 | 1140 | 1128 | -12 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
50 | 2015 | 1 | 1 | 4 | AA | 54 | N3CWAA | LAS | ORD | 910 | 906 | -4 | 16 | 922 | 215 | 193 | 170 | 1514 | 1412 | 7 | 1445 | 1419 | -26 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
51 | 2015 | 1 | 1 | 4 | AA | 55 | N3JJAA | ORD | LAS | 1820 | 1924 | 64 | 13 | 1937 | 230 | 225 | 205 | 1514 | 2102 | 7 | 2010 | 2109 | 59 | 0 | 0 | [null] | 0 | 0 | 59 | 0 | 0 |
52 | 2015 | 1 | 1 | 4 | AA | 58 | N5DFAA | KOA | LAX | 1400 | 1400 | 0 | 22 | 1422 | 310 | 320 | 276 | 2504 | 2058 | 22 | 2110 | 2120 | 10 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
53 | 2015 | 1 | 1 | 4 | AA | 59 | N5DFAA | LAX | KOA | 840 | 837 | -3 | 17 | 854 | 350 | 352 | 332 | 2504 | 1226 | 3 | 1230 | 1229 | -1 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
54 | 2015 | 1 | 1 | 4 | AA | 60 | N372AA | DFW | MIA | 1754 | 1955 | 121 | 15 | 2010 | 160 | 151 | 133 | 1121 | 2323 | 3 | 2134 | 2326 | 112 | 0 | 0 | [null] | 0 | 0 | 112 | 0 | 0 |
55 | 2015 | 1 | 1 | 4 | AA | 61 | N850AA | MIA | DFW | 715 | 719 | 4 | 13 | 732 | 199 | 192 | 167 | 1121 | 919 | 12 | 934 | 931 | -3 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
56 | 2015 | 1 | 1 | 4 | AA | 63 | N3ECAA | MIA | PHX | 1950 | 1955 | 5 | 23 | 2018 | 317 | 302 | 271 | 1972 | 2249 | 8 | 2307 | 2257 | -10 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
57 | 2015 | 1 | 1 | 4 | AA | 64 | N3HXAA | DFW | JFK | 1025 | 1041 | 16 | 13 | 1054 | 200 | 187 | 169 | 1391 | 1443 | 5 | 1445 | 1448 | 3 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
58 | 2015 | 1 | 1 | 4 | AA | 65 | N3HLAA | JFK | DFW | 1615 | 1625 | 10 | 19 | 1644 | 255 | 294 | 227 | 1391 | 1931 | 48 | 1930 | 2019 | 49 | 0 | 0 | [null] | 39 | 0 | 10 | 0 | 0 |
59 | 2015 | 1 | 1 | 4 | AA | 66 | N631AA | SJU | JFK | 1151 | 1142 | -9 | 22 | 1204 | 245 | 247 | 221 | 1598 | 1445 | 4 | 1456 | 1449 | -7 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
60 | 2015 | 1 | 1 | 4 | AA | 67 | N631AA | JFK | SJU | 1605 | 1629 | 24 | 23 | 1652 | 235 | 211 | 182 | 1598 | 2054 | 6 | 2100 | 2100 | 0 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
61 | 2015 | 1 | 1 | 4 | AA | 68 | N5EGAA | SFO | MIA | 700 | 703 | 3 | 14 | 717 | 324 | 317 | 295 | 2585 | 1512 | 8 | 1524 | 1520 | -4 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
62 | 2015 | 1 | 1 | 4 | AA | 69 | N3HTAA | MIA | SFO | 1835 | 1836 | 1 | 12 | 1848 | 386 | 359 | 342 | 2585 | 2130 | 5 | 2201 | 2135 | -26 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
63 | 2015 | 1 | 1 | 4 | AA | 70 | N5BTAA | SAN | DFW | 705 | 659 | -6 | 22 | 721 | 180 | 171 | 137 | 1171 | 1138 | 12 | 1205 | 1150 | -15 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
64 | 2015 | 1 | 1 | 4 | AA | 71 | N3GHAA | DFW | SAN | 1805 | 1803 | -2 | 14 | 1817 | 180 | 176 | 160 | 1171 | 1857 | 2 | 1905 | 1859 | -6 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
65 | 2015 | 1 | 1 | 4 | AA | 72 | N5EKAA | DFW | MCO | 600 | 606 | 6 | 18 | 624 | 145 | 142 | 120 | 985 | 924 | 4 | 925 | 928 | 3 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
66 | 2015 | 1 | 1 | 4 | AA | 72 | N5EKAA | MCO | DFW | 1020 | 1015 | -5 | 16 | 1031 | 175 | 171 | 143 | 985 | 1154 | 12 | 1215 | 1206 | -9 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
67 | 2015 | 1 | 1 | 4 | AA | 73 | N3FBAA | PHL | CLT | 1005 | 957 | -8 | 26 | 1023 | 120 | 108 | 76 | 449 | 1139 | 6 | 1205 | 1145 | -20 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
68 | 2015 | 1 | 1 | 4 | AA | 74 | N488AA | CLE | DFW | 1555 | 1552 | -3 | 11 | 1603 | 190 | 181 | 161 | 1021 | 1744 | 9 | 1805 | 1753 | -12 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
69 | 2015 | 1 | 1 | 4 | AA | 74 | N505AA | DFW | CLE | 1145 | [null] | [null] | [null] | [null] | 150 | [null] | [null] | 1021 | [null] | [null] | 1515 | [null] | [null] | 0 | 1 | B | [null] | [null] | [null] | [null] | [null] |
70 | 2015 | 1 | 1 | 4 | AA | 75 | N3HPAA | IAD | LAX | 1830 | 1822 | -8 | 17 | 1839 | 360 | 358 | 335 | 2288 | 2114 | 6 | 2130 | 2120 | -10 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
71 | 2015 | 1 | 1 | 4 | AA | 76 | N3HPAA | LAX | IAD | 950 | 946 | -4 | 18 | 1004 | 285 | 265 | 243 | 2288 | 1707 | 4 | 1735 | 1711 | -24 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
72 | 2015 | 1 | 1 | 4 | AA | 76 | N3HPAA | SFO | LAX | 730 | 723 | -7 | 15 | 738 | 85 | 97 | 49 | 337 | 827 | 33 | 855 | 900 | 5 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
73 | 2015 | 1 | 1 | 4 | AA | 79 | N520AA | DFW | SMF | 2140 | 2219 | 39 | 30 | 2249 | 225 | 224 | 188 | 1431 | 2357 | 6 | 2325 | 3 | 38 | 0 | 0 | [null] | 0 | 0 | 0 | 38 | 0 |
74 | 2015 | 1 | 1 | 4 | AA | 80 | N4XTAA | TUS | DFW | 1655 | 1741 | 46 | 10 | 1751 | 130 | 130 | 105 | 813 | 2036 | 15 | 2005 | 2051 | 46 | 0 | 0 | [null] | 0 | 0 | 0 | 46 | 0 |
75 | 2015 | 1 | 1 | 4 | AA | 81 | N4XCAA | DFW | TUS | 2245 | 2308 | 23 | 13 | 2321 | 140 | 140 | 123 | 813 | 24 | 4 | 5 | 28 | 23 | 0 | 0 | [null] | 0 | 0 | 0 | 23 | 0 |
76 | 2015 | 1 | 1 | 4 | AA | 83 | N4XBAA | MCO | DFW | 735 | 733 | -2 | 16 | 749 | 180 | 174 | 146 | 985 | 915 | 12 | 935 | 927 | -8 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
77 | 2015 | 1 | 1 | 4 | AA | 84 | N3AXAA | BOS | JFK | 940 | 930 | -10 | 15 | 945 | 75 | 69 | 46 | 187 | 1031 | 8 | 1055 | 1039 | -16 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
78 | 2015 | 1 | 1 | 4 | AA | 84 | N3AXAA | JFK | BOS | 730 | 734 | 4 | 17 | 751 | 70 | 65 | 44 | 187 | 835 | 4 | 840 | 839 | -1 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
79 | 2015 | 1 | 1 | 4 | AA | 85 | N785AA | JFK | SFO | 1530 | 1524 | -6 | 20 | 1544 | 400 | 386 | 361 | 2586 | 1845 | 5 | 1910 | 1850 | -20 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
80 | 2015 | 1 | 1 | 4 | AA | 86 | N3GTAA | PDX | ORD | 705 | 701 | -4 | 15 | 716 | 235 | 230 | 208 | 1739 | 1244 | 7 | 1300 | 1251 | -9 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
81 | 2015 | 1 | 1 | 4 | AA | 87 | N490AA | ORD | RNO | 1845 | 1840 | -5 | 11 | 1851 | 265 | 228 | 213 | 1671 | 2024 | 4 | 2110 | 2028 | -42 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
82 | 2015 | 1 | 1 | 4 | AA | 89 | N3KVAA | IAH | MIA | 520 | 618 | 58 | 19 | 637 | 141 | 137 | 111 | 964 | 928 | 7 | 841 | 935 | 54 | 0 | 0 | [null] | 0 | 0 | 54 | 0 | 0 |
83 | 2015 | 1 | 1 | 4 | AA | 92 | N3KHAA | SAN | ORD | 900 | 900 | 0 | 15 | 915 | 240 | 210 | 189 | 1723 | 1424 | 6 | 1500 | 1430 | -30 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
84 | 2015 | 1 | 1 | 4 | AA | 93 | N3HHAA | ORD | SAN | 1720 | 1805 | 45 | 10 | 1815 | 255 | 222 | 210 | 1723 | 1945 | 2 | 1935 | 1947 | 12 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
85 | 2015 | 1 | 1 | 4 | AA | 94 | N3ANAA | SAN | JFK | 755 | 810 | 15 | 13 | 823 | 320 | 281 | 262 | 2446 | 1545 | 6 | 1615 | 1551 | -24 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
86 | 2015 | 1 | 1 | 4 | AA | 95 | N3ANAA | JFK | SAN | 1720 | 1749 | 29 | 18 | 1807 | 380 | 386 | 363 | 2446 | 2110 | 5 | 2040 | 2115 | 35 | 0 | 0 | [null] | 6 | 0 | 29 | 0 | 0 |
87 | 2015 | 1 | 1 | 4 | AA | 96 | N553AA | DFW | STL | 820 | 831 | 11 | 21 | 852 | 95 | 94 | 70 | 550 | 1002 | 3 | 955 | 1005 | 10 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
88 | 2015 | 1 | 1 | 4 | AA | 96 | N553AA | STL | DFW | 1035 | 1051 | 16 | 15 | 1106 | 115 | 131 | 98 | 550 | 1244 | 18 | 1230 | 1302 | 32 | 0 | 0 | [null] | 16 | 0 | 6 | 10 | 0 |
89 | 2015 | 1 | 1 | 4 | AA | 97 | N3ENAA | DCA | DFW | 845 | 842 | -3 | 14 | 856 | 215 | 210 | 185 | 1192 | 1101 | 11 | 1120 | 1112 | -8 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
90 | 2015 | 1 | 1 | 4 | AA | 99 | N3KMAA | ORD | PDX | 1550 | 1553 | 3 | 11 | 1604 | 275 | 253 | 239 | 1739 | 1803 | 3 | 1825 | 1806 | -19 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
91 | 2015 | 1 | 1 | 4 | AA | 102 | N376AA | HNL | DFW | 1920 | 2356 | 276 | 17 | 13 | 445 | 445 | 417 | 3784 | 1110 | 11 | 645 | 1121 | 276 | 0 | 0 | [null] | 0 | 0 | 0 | 276 | 0 |
92 | 2015 | 1 | 1 | 4 | AA | 103 | N4XSAA | DFW | MSY | 1410 | 1433 | 23 | 13 | 1446 | 80 | 80 | 62 | 447 | 1548 | 5 | 1530 | 1553 | 23 | 0 | 0 | [null] | 0 | 0 | 23 | 0 | 0 |
93 | 2015 | 1 | 1 | 4 | AA | 103 | N4XSAA | MSY | DFW | 1610 | 1624 | 14 | 8 | 1632 | 100 | 91 | 74 | 447 | 1746 | 9 | 1750 | 1755 | 5 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
94 | 2015 | 1 | 1 | 4 | AA | 110 | N3LRAA | SNA | ORD | 805 | 801 | -4 | 7 | 808 | 230 | 211 | 194 | 1726 | 1322 | 10 | 1355 | 1332 | -23 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
95 | 2015 | 1 | 1 | 4 | AA | 111 | N3LYAA | ORD | SNA | 1720 | 1719 | -1 | 11 | 1730 | 260 | 237 | 222 | 1726 | 1912 | 4 | 1940 | 1916 | -24 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
96 | 2015 | 1 | 1 | 4 | AA | 112 | N857AA | DEN | MIA | 1015 | 1017 | 2 | 19 | 1036 | 224 | 229 | 199 | 1709 | 1555 | 11 | 1559 | 1606 | 7 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
97 | 2015 | 1 | 1 | 4 | AA | 113 | N3GRAA | MIA | DEN | 2115 | 2115 | 0 | 21 | 2136 | 269 | 269 | 235 | 1709 | 2331 | 13 | 2344 | 2344 | 0 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
98 | 2015 | 1 | 1 | 4 | AA | 115 | N3CTAA | LAX | MIA | 105 | 103 | -2 | 14 | 117 | 286 | 276 | 255 | 2342 | 832 | 7 | 851 | 839 | -12 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
99 | 2015 | 1 | 1 | 4 | AA | 116 | N383AA | OGG | DFW | 1955 | 2016 | 21 | 10 | 2026 | 430 | 433 | 418 | 3711 | 724 | 5 | 705 | 729 | 24 | 0 | 0 | [null] | 3 | 0 | 21 | 0 | 0 |
100 | 2015 | 1 | 1 | 4 | AA | 117 | N793AA | JFK | LAX | 1445 | 1438 | -7 | 28 | 1506 | 390 | 389 | 355 | 2475 | 1801 | 6 | 1815 | 1807 | -8 | 0 | 0 | [null] | [null] | [null] | [null] | [null] | [null] |
Joins and Queries Across Multiple Databases#
Now we can run queries that execute through multiple sources.
Let’s try to find the TAIL_NUMBER and Departing City for all the flights by joining the two tables:
flight_vertica (stored in Vertica)
airports (stored in MySQL)
[9]:
%%sql
/* Fetch TAIL_NUMBER and CITY after Joining the flight_vertica table with airports table in MySQL database. */
SELECT flight_vertica.TAIL_NUMBER, airports.CITY AS Departing_City
FROM flight_vertica
INNER JOIN &&& airports &&&
ON flight_vertica.ORIGIN_AIRPORT = airports.IATA_CODE;
[9]:
Abc TAIL_NUMBERVarchar(20) | Abc Departing_CityVarchar(60) | |
1 | N787AA | New York |
2 | N795AA | Los Angeles |
3 | N798AA | New York |
4 | N799AA | Los Angeles |
5 | N376AA | Dallas-Fort Worth |
6 | N398AA | Kahului |
7 | N398AA | Dallas-Fort Worth |
8 | N368AA | Honolulu |
9 | N792AA | New York |
10 | N796AA | Los Angeles |
11 | N792AA | Los Angeles |
12 | N5ERAA | Kahului |
13 | N783AA | New York |
14 | N783AA | San Francisco |
15 | N019AA | Atlanta |
16 | N786AA | New York |
17 | N789AA | San Francisco |
18 | N795AA | New York |
19 | N786AA | Los Angeles |
20 | N371AA | Dallas-Fort Worth |
21 | N371AA | San Francisco |
22 | N784AA | San Francisco |
23 | N3AVAA | Boston |
24 | N5DUAA | Dallas-Fort Worth |
25 | N358AA | Los Angeles |
26 | N455AA | Ontario |
27 | N568AA | Dallas-Fort Worth |
28 | N795AA | Los Angeles |
29 | N5ESAA | Los Angeles |
30 | N794AA | Los Angeles |
31 | N3ALAA | Dallas-Fort Worth |
32 | N3ALAA | Hayden |
33 | N3EKAA | San Diego |
34 | N483AA | Dallas-Fort Worth |
35 | N3BYAA | Arlington |
36 | N3KEAA | Chicago |
37 | N3GPAA | Seattle |
38 | N3BNAA | Dallas-Fort Worth |
39 | N3KCAA | Detroit |
40 | N3AJAA | Las Vegas |
41 | N3ELAA | New York |
42 | N3CSAA | San Jose |
43 | N3KSAA | Chicago |
44 | N3JXAA | San Diego |
45 | N5ELAA | Dallas-Fort Worth |
46 | N570AA | Salt Lake City |
47 | N471AA | Dallas-Fort Worth |
48 | N3LMAA | Los Angeles |
49 | N3LHAA | Arlington |
50 | N3CWAA | Las Vegas |
51 | N3JJAA | Chicago |
52 | N5DFAA | Kailua/Kona |
53 | N5DFAA | Los Angeles |
54 | N372AA | Dallas-Fort Worth |
55 | N850AA | Miami |
56 | N3ECAA | Miami |
57 | N3HXAA | Dallas-Fort Worth |
58 | N3HLAA | New York |
59 | N631AA | San Juan |
60 | N631AA | New York |
61 | N5EGAA | San Francisco |
62 | N3HTAA | Miami |
63 | N5BTAA | San Diego |
64 | N3GHAA | Dallas-Fort Worth |
65 | N5EKAA | Dallas-Fort Worth |
66 | N5EKAA | Orlando |
67 | N3FBAA | Philadelphia |
68 | N488AA | Cleveland |
69 | N505AA | Dallas-Fort Worth |
70 | N3HPAA | Chantilly |
71 | N3HPAA | Los Angeles |
72 | N3HPAA | San Francisco |
73 | N520AA | Dallas-Fort Worth |
74 | N4XTAA | Tucson |
75 | N4XCAA | Dallas-Fort Worth |
76 | N4XBAA | Orlando |
77 | N3AXAA | Boston |
78 | N3AXAA | New York |
79 | N785AA | New York |
80 | N3GTAA | Portland |
81 | N490AA | Chicago |
82 | N3KVAA | Houston |
83 | N3KHAA | San Diego |
84 | N3HHAA | Chicago |
85 | N3ANAA | San Diego |
86 | N3ANAA | New York |
87 | N553AA | Dallas-Fort Worth |
88 | N553AA | St Louis |
89 | N3ENAA | Arlington |
90 | N3KMAA | Chicago |
91 | N376AA | Honolulu |
92 | N4XSAA | Dallas-Fort Worth |
93 | N4XSAA | New Orleans |
94 | N3LRAA | Santa Ana |
95 | N3LYAA | Chicago |
96 | N857AA | Denver |
97 | N3GRAA | Miami |
98 | N3CTAA | Los Angeles |
99 | N383AA | Kahului |
100 | N793AA | New York |
Let’s try another query to find the TAIL_NUMBER and AIRLINE of all the flights by joining the two tables:
flight_vertica (stored in Vertica)
airline (stored in PostgreSQL)
[30]:
%%sql
/* Fetch TAIL_NUMBER and AIRLINE after Joining the flight_vertica table with airline table in PostgreSQL database. */
SELECT flight_vertica.TAIL_NUMBER, airline.AIRLINE
FROM flight_vertica
INNER JOIN $$$ airline $$$
ON flight_vertica.AIRLINE = airline.IATA_CODE;
[30]:
Abc TAIL_NUMBERVarchar(20) | Abc AIRLINEVarchar(1024) | |
1 | N787AA | American Airlines Inc. |
2 | N787AA | American Airlines Inc. |
3 | N795AA | American Airlines Inc. |
4 | N795AA | American Airlines Inc. |
5 | N798AA | American Airlines Inc. |
6 | N798AA | American Airlines Inc. |
7 | N799AA | American Airlines Inc. |
8 | N799AA | American Airlines Inc. |
9 | N376AA | American Airlines Inc. |
10 | N376AA | American Airlines Inc. |
11 | N398AA | American Airlines Inc. |
12 | N398AA | American Airlines Inc. |
13 | N398AA | American Airlines Inc. |
14 | N398AA | American Airlines Inc. |
15 | N368AA | American Airlines Inc. |
16 | N368AA | American Airlines Inc. |
17 | N792AA | American Airlines Inc. |
18 | N792AA | American Airlines Inc. |
19 | N796AA | American Airlines Inc. |
20 | N796AA | American Airlines Inc. |
21 | N792AA | American Airlines Inc. |
22 | N792AA | American Airlines Inc. |
23 | N5ERAA | American Airlines Inc. |
24 | N5ERAA | American Airlines Inc. |
25 | N783AA | American Airlines Inc. |
26 | N783AA | American Airlines Inc. |
27 | N783AA | American Airlines Inc. |
28 | N783AA | American Airlines Inc. |
29 | N019AA | American Airlines Inc. |
30 | N019AA | American Airlines Inc. |
31 | N786AA | American Airlines Inc. |
32 | N786AA | American Airlines Inc. |
33 | N789AA | American Airlines Inc. |
34 | N789AA | American Airlines Inc. |
35 | N795AA | American Airlines Inc. |
36 | N795AA | American Airlines Inc. |
37 | N786AA | American Airlines Inc. |
38 | N786AA | American Airlines Inc. |
39 | N371AA | American Airlines Inc. |
40 | N371AA | American Airlines Inc. |
41 | N371AA | American Airlines Inc. |
42 | N371AA | American Airlines Inc. |
43 | N784AA | American Airlines Inc. |
44 | N784AA | American Airlines Inc. |
45 | N3AVAA | American Airlines Inc. |
46 | N3AVAA | American Airlines Inc. |
47 | N5DUAA | American Airlines Inc. |
48 | N5DUAA | American Airlines Inc. |
49 | N358AA | American Airlines Inc. |
50 | N358AA | American Airlines Inc. |
51 | N455AA | American Airlines Inc. |
52 | N455AA | American Airlines Inc. |
53 | N568AA | American Airlines Inc. |
54 | N568AA | American Airlines Inc. |
55 | N795AA | American Airlines Inc. |
56 | N795AA | American Airlines Inc. |
57 | N5ESAA | American Airlines Inc. |
58 | N5ESAA | American Airlines Inc. |
59 | N794AA | American Airlines Inc. |
60 | N794AA | American Airlines Inc. |
61 | N3ALAA | American Airlines Inc. |
62 | N3ALAA | American Airlines Inc. |
63 | N3ALAA | American Airlines Inc. |
64 | N3ALAA | American Airlines Inc. |
65 | N3EKAA | American Airlines Inc. |
66 | N3EKAA | American Airlines Inc. |
67 | N483AA | American Airlines Inc. |
68 | N483AA | American Airlines Inc. |
69 | N3BYAA | American Airlines Inc. |
70 | N3BYAA | American Airlines Inc. |
71 | N3KEAA | American Airlines Inc. |
72 | N3KEAA | American Airlines Inc. |
73 | N3GPAA | American Airlines Inc. |
74 | N3GPAA | American Airlines Inc. |
75 | N3BNAA | American Airlines Inc. |
76 | N3BNAA | American Airlines Inc. |
77 | N3KCAA | American Airlines Inc. |
78 | N3KCAA | American Airlines Inc. |
79 | N3AJAA | American Airlines Inc. |
80 | N3AJAA | American Airlines Inc. |
81 | N3ELAA | American Airlines Inc. |
82 | N3ELAA | American Airlines Inc. |
83 | N3CSAA | American Airlines Inc. |
84 | N3CSAA | American Airlines Inc. |
85 | N3KSAA | American Airlines Inc. |
86 | N3KSAA | American Airlines Inc. |
87 | N3JXAA | American Airlines Inc. |
88 | N3JXAA | American Airlines Inc. |
89 | N5ELAA | American Airlines Inc. |
90 | N5ELAA | American Airlines Inc. |
91 | N570AA | American Airlines Inc. |
92 | N570AA | American Airlines Inc. |
93 | N471AA | American Airlines Inc. |
94 | N471AA | American Airlines Inc. |
95 | N3LMAA | American Airlines Inc. |
96 | N3LMAA | American Airlines Inc. |
97 | N3LHAA | American Airlines Inc. |
98 | N3LHAA | American Airlines Inc. |
99 | N3CWAA | American Airlines Inc. |
100 | N3CWAA | American Airlines Inc. |
We can even try queries that require multiple joins.
In the following example, we try to get the TAIL_NUMBER, AIRLINE, and CITY details for all the flights by joining:
flight_local table (stored in Vertica)
airline table (stored in PostgreSQL)
airports table (stored in MySQL)
[10]:
%%sql
/* Fetch FLIGHT_NUMBER, AIRLINE and STATE after Joining the flight_vertica table with two other tables from different databases. */
SELECT flight_vertica.FLIGHT_NUMBER, airline.AIRLINE, airports.STATE
FROM flight_vertica
INNER JOIN $$$ airline $$$
ON flight_vertica.AIRLINE = airline.IATA_CODE
INNER JOIN &&& airports &&&
ON flight_vertica.ORIGIN_AIRPORT = airports.IATA_CODE;
[10]:
123 FLIGHT_NUMBERInteger | Abc AIRLINEVarchar(1024) | Abc STATEVarchar(2) | |
1 | 1 | American Airlines Inc. | NY |
2 | 1 | American Airlines Inc. | NY |
3 | 2 | American Airlines Inc. | CA |
4 | 2 | American Airlines Inc. | CA |
5 | 3 | American Airlines Inc. | NY |
6 | 3 | American Airlines Inc. | NY |
7 | 4 | American Airlines Inc. | CA |
8 | 4 | American Airlines Inc. | CA |
9 | 5 | American Airlines Inc. | TX |
10 | 5 | American Airlines Inc. | TX |
11 | 6 | American Airlines Inc. | HI |
12 | 6 | American Airlines Inc. | HI |
13 | 7 | American Airlines Inc. | TX |
14 | 7 | American Airlines Inc. | TX |
15 | 8 | American Airlines Inc. | HI |
16 | 8 | American Airlines Inc. | HI |
17 | 9 | American Airlines Inc. | NY |
18 | 9 | American Airlines Inc. | NY |
19 | 10 | American Airlines Inc. | CA |
20 | 10 | American Airlines Inc. | CA |
21 | 12 | American Airlines Inc. | CA |
22 | 12 | American Airlines Inc. | CA |
23 | 14 | American Airlines Inc. | HI |
24 | 14 | American Airlines Inc. | HI |
25 | 15 | American Airlines Inc. | NY |
26 | 15 | American Airlines Inc. | NY |
27 | 16 | American Airlines Inc. | CA |
28 | 16 | American Airlines Inc. | CA |
29 | 17 | American Airlines Inc. | GA |
30 | 17 | American Airlines Inc. | GA |
31 | 19 | American Airlines Inc. | NY |
32 | 19 | American Airlines Inc. | NY |
33 | 20 | American Airlines Inc. | CA |
34 | 20 | American Airlines Inc. | CA |
35 | 21 | American Airlines Inc. | NY |
36 | 21 | American Airlines Inc. | NY |
37 | 22 | American Airlines Inc. | CA |
38 | 22 | American Airlines Inc. | CA |
39 | 23 | American Airlines Inc. | TX |
40 | 23 | American Airlines Inc. | TX |
41 | 23 | American Airlines Inc. | CA |
42 | 23 | American Airlines Inc. | CA |
43 | 24 | American Airlines Inc. | CA |
44 | 24 | American Airlines Inc. | CA |
45 | 25 | American Airlines Inc. | MA |
46 | 25 | American Airlines Inc. | MA |
47 | 27 | American Airlines Inc. | TX |
48 | 27 | American Airlines Inc. | TX |
49 | 28 | American Airlines Inc. | CA |
50 | 28 | American Airlines Inc. | CA |
51 | 29 | American Airlines Inc. | CA |
52 | 29 | American Airlines Inc. | CA |
53 | 29 | American Airlines Inc. | TX |
54 | 29 | American Airlines Inc. | TX |
55 | 30 | American Airlines Inc. | CA |
56 | 30 | American Airlines Inc. | CA |
57 | 31 | American Airlines Inc. | CA |
58 | 31 | American Airlines Inc. | CA |
59 | 32 | American Airlines Inc. | CA |
60 | 32 | American Airlines Inc. | CA |
61 | 35 | American Airlines Inc. | TX |
62 | 35 | American Airlines Inc. | TX |
63 | 35 | American Airlines Inc. | CO |
64 | 35 | American Airlines Inc. | CO |
65 | 36 | American Airlines Inc. | CA |
66 | 36 | American Airlines Inc. | CA |
67 | 37 | American Airlines Inc. | TX |
68 | 37 | American Airlines Inc. | TX |
69 | 40 | American Airlines Inc. | VA |
70 | 40 | American Airlines Inc. | VA |
71 | 41 | American Airlines Inc. | IL |
72 | 41 | American Airlines Inc. | IL |
73 | 42 | American Airlines Inc. | WA |
74 | 42 | American Airlines Inc. | WA |
75 | 43 | American Airlines Inc. | TX |
76 | 43 | American Airlines Inc. | TX |
77 | 43 | American Airlines Inc. | MI |
78 | 43 | American Airlines Inc. | MI |
79 | 44 | American Airlines Inc. | NV |
80 | 44 | American Airlines Inc. | NV |
81 | 45 | American Airlines Inc. | NY |
82 | 45 | American Airlines Inc. | NY |
83 | 46 | American Airlines Inc. | CA |
84 | 46 | American Airlines Inc. | CA |
85 | 47 | American Airlines Inc. | IL |
86 | 47 | American Airlines Inc. | IL |
87 | 48 | American Airlines Inc. | CA |
88 | 48 | American Airlines Inc. | CA |
89 | 49 | American Airlines Inc. | TX |
90 | 49 | American Airlines Inc. | TX |
91 | 50 | American Airlines Inc. | UT |
92 | 50 | American Airlines Inc. | UT |
93 | 51 | American Airlines Inc. | TX |
94 | 51 | American Airlines Inc. | TX |
95 | 52 | American Airlines Inc. | CA |
96 | 52 | American Airlines Inc. | CA |
97 | 53 | American Airlines Inc. | VA |
98 | 53 | American Airlines Inc. | VA |
99 | 54 | American Airlines Inc. | NV |
100 | 54 | American Airlines Inc. | NV |
Pandas.DataFrame#
The joins also work with pandas.Dataframe. We can perform the same query that required multiple joins, but now with a local Pandas dataframe.
We will first read the local CSV file.
[35]:
# Create a Pandas Data Frame after importing the csv file "passengers.csv"
import pandas as pd
passengers_pandas = pd.read_csv('passengers.csv')
passengers_pandas
[35]:
FLIGHT_NUMBER | PASSENGER_COUNT | |
---|---|---|
0 | 98 | 293 |
1 | 2336 | 222 |
2 | 840 | 188 |
3 | 258 | 143 |
4 | 135 | 267 |
... | ... | ... |
5401 | 4106 | 266 |
5402 | 4113 | 157 |
5403 | 979 | 226 |
5404 | 5430 | 251 |
5405 | 4157 | 228 |
5406 rows × 2 columns
We can now perform the same query involving the three tables:
flight_vertica table (stored in Vertica)
passengers_pandas table (pandas.DataFrame stored in-memory)
airline table (stored in PostgreSQL)
airports table (stored in MySQL)
[33]:
%%sql
SELECT
flight_vertica.TAIL_NUMBER,
airline.AIRLINE,
airports.CITY,
:passengers_pandas.PASSENGER_COUNT
FROM flight_vertica
INNER JOIN $$$ airline $$$
ON flight_vertica.AIRLINE = airline.IATA_CODE
INNER JOIN &&& airports &&&
ON flight_vertica.ORIGIN_AIRPORT = airports.IATA_CODE
INNER JOIN :passengers_pandas
ON flight_vertica.FLIGHT_NUMBER = :passengers_pandas.FLIGHT_NUMBER;
[33]:
Abc TAIL_NUMBERVarchar(20) | Abc AIRLINEVarchar(1024) | Abc CITYVarchar(60) | 123 PASSENGER_COUNTInteger | |
1 | N787AA | American Airlines Inc. | New York | 252 |
2 | N787AA | American Airlines Inc. | New York | 252 |
3 | N795AA | American Airlines Inc. | Los Angeles | 193 |
4 | N795AA | American Airlines Inc. | Los Angeles | 193 |
5 | N798AA | American Airlines Inc. | New York | 201 |
6 | N798AA | American Airlines Inc. | New York | 201 |
7 | N799AA | American Airlines Inc. | Los Angeles | 210 |
8 | N799AA | American Airlines Inc. | Los Angeles | 210 |
9 | N376AA | American Airlines Inc. | Dallas-Fort Worth | 190 |
10 | N376AA | American Airlines Inc. | Dallas-Fort Worth | 190 |
11 | N398AA | American Airlines Inc. | Kahului | 181 |
12 | N398AA | American Airlines Inc. | Kahului | 181 |
13 | N398AA | American Airlines Inc. | Dallas-Fort Worth | 195 |
14 | N398AA | American Airlines Inc. | Dallas-Fort Worth | 195 |
15 | N368AA | American Airlines Inc. | Honolulu | 117 |
16 | N368AA | American Airlines Inc. | Honolulu | 117 |
17 | N792AA | American Airlines Inc. | New York | 221 |
18 | N792AA | American Airlines Inc. | New York | 221 |
19 | N796AA | American Airlines Inc. | Los Angeles | 245 |
20 | N796AA | American Airlines Inc. | Los Angeles | 245 |
21 | N792AA | American Airlines Inc. | Los Angeles | 280 |
22 | N792AA | American Airlines Inc. | Los Angeles | 280 |
23 | N5ERAA | American Airlines Inc. | Kahului | 259 |
24 | N5ERAA | American Airlines Inc. | Kahului | 259 |
25 | N783AA | American Airlines Inc. | New York | 184 |
26 | N783AA | American Airlines Inc. | New York | 184 |
27 | N783AA | American Airlines Inc. | San Francisco | 173 |
28 | N783AA | American Airlines Inc. | San Francisco | 173 |
29 | N019AA | American Airlines Inc. | Atlanta | 244 |
30 | N019AA | American Airlines Inc. | Atlanta | 244 |
31 | N786AA | American Airlines Inc. | New York | 235 |
32 | N786AA | American Airlines Inc. | New York | 235 |
33 | N789AA | American Airlines Inc. | San Francisco | 156 |
34 | N789AA | American Airlines Inc. | San Francisco | 156 |
35 | N795AA | American Airlines Inc. | New York | 204 |
36 | N795AA | American Airlines Inc. | New York | 204 |
37 | N786AA | American Airlines Inc. | Los Angeles | 210 |
38 | N786AA | American Airlines Inc. | Los Angeles | 210 |
39 | N371AA | American Airlines Inc. | Dallas-Fort Worth | 131 |
40 | N371AA | American Airlines Inc. | Dallas-Fort Worth | 131 |
41 | N371AA | American Airlines Inc. | San Francisco | 131 |
42 | N371AA | American Airlines Inc. | San Francisco | 131 |
43 | N784AA | American Airlines Inc. | San Francisco | 152 |
44 | N784AA | American Airlines Inc. | San Francisco | 152 |
45 | N3AVAA | American Airlines Inc. | Boston | 155 |
46 | N3AVAA | American Airlines Inc. | Boston | 155 |
47 | N5DUAA | American Airlines Inc. | Dallas-Fort Worth | 210 |
48 | N5DUAA | American Airlines Inc. | Dallas-Fort Worth | 210 |
49 | N358AA | American Airlines Inc. | Los Angeles | 224 |
50 | N358AA | American Airlines Inc. | Los Angeles | 224 |
51 | N455AA | American Airlines Inc. | Ontario | 141 |
52 | N455AA | American Airlines Inc. | Ontario | 141 |
53 | N568AA | American Airlines Inc. | Dallas-Fort Worth | 141 |
54 | N568AA | American Airlines Inc. | Dallas-Fort Worth | 141 |
55 | N795AA | American Airlines Inc. | Los Angeles | 238 |
56 | N795AA | American Airlines Inc. | Los Angeles | 238 |
57 | N5ESAA | American Airlines Inc. | Los Angeles | 148 |
58 | N5ESAA | American Airlines Inc. | Los Angeles | 148 |
59 | N794AA | American Airlines Inc. | Los Angeles | 200 |
60 | N794AA | American Airlines Inc. | Los Angeles | 200 |
61 | N3ALAA | American Airlines Inc. | Dallas-Fort Worth | 138 |
62 | N3ALAA | American Airlines Inc. | Dallas-Fort Worth | 138 |
63 | N3ALAA | American Airlines Inc. | Hayden | 138 |
64 | N3ALAA | American Airlines Inc. | Hayden | 138 |
65 | N3EKAA | American Airlines Inc. | San Diego | 258 |
66 | N3EKAA | American Airlines Inc. | San Diego | 258 |
67 | N483AA | American Airlines Inc. | Dallas-Fort Worth | 272 |
68 | N483AA | American Airlines Inc. | Dallas-Fort Worth | 272 |
69 | N3BYAA | American Airlines Inc. | Arlington | 189 |
70 | N3BYAA | American Airlines Inc. | Arlington | 189 |
71 | N3KEAA | American Airlines Inc. | Chicago | 161 |
72 | N3KEAA | American Airlines Inc. | Chicago | 161 |
73 | N3GPAA | American Airlines Inc. | Seattle | 186 |
74 | N3GPAA | American Airlines Inc. | Seattle | 186 |
75 | N3BNAA | American Airlines Inc. | Dallas-Fort Worth | 209 |
76 | N3BNAA | American Airlines Inc. | Dallas-Fort Worth | 209 |
77 | N3KCAA | American Airlines Inc. | Detroit | 209 |
78 | N3KCAA | American Airlines Inc. | Detroit | 209 |
79 | N3AJAA | American Airlines Inc. | Las Vegas | 118 |
80 | N3AJAA | American Airlines Inc. | Las Vegas | 118 |
81 | N3ELAA | American Airlines Inc. | New York | 208 |
82 | N3ELAA | American Airlines Inc. | New York | 208 |
83 | N3CSAA | American Airlines Inc. | San Jose | 266 |
84 | N3CSAA | American Airlines Inc. | San Jose | 266 |
85 | N3KSAA | American Airlines Inc. | Chicago | 278 |
86 | N3KSAA | American Airlines Inc. | Chicago | 278 |
87 | N3JXAA | American Airlines Inc. | San Diego | 213 |
88 | N3JXAA | American Airlines Inc. | San Diego | 213 |
89 | N5ELAA | American Airlines Inc. | Dallas-Fort Worth | 207 |
90 | N5ELAA | American Airlines Inc. | Dallas-Fort Worth | 207 |
91 | N570AA | American Airlines Inc. | Salt Lake City | 176 |
92 | N570AA | American Airlines Inc. | Salt Lake City | 176 |
93 | N471AA | American Airlines Inc. | Dallas-Fort Worth | 234 |
94 | N471AA | American Airlines Inc. | Dallas-Fort Worth | 234 |
95 | N3LMAA | American Airlines Inc. | Los Angeles | 149 |
96 | N3LMAA | American Airlines Inc. | Los Angeles | 149 |
97 | N3LHAA | American Airlines Inc. | Arlington | 277 |
98 | N3LHAA | American Airlines Inc. | Arlington | 277 |
99 | N3CWAA | American Airlines Inc. | Las Vegas | 193 |
100 | N3CWAA | American Airlines Inc. | Las Vegas | 193 |
Conclusion#
With the combination of Verticapy and DBLINK, we can now work with multiple datasets stored in different databases. We can work simultaneously with external tables, Vertica tables, and Pandas DataFrame in a single query! There is no need to materialize the table before use because it’s all taken care of in the background.
The cherry on the cake is the ease-of-use that is enabled by VerticaPy and its Python-like syntax.
Queries that required paragraph upon paragraph to execute can now be done efficiently with only a few intuitive lines of code.
This new functionality opens up many possibilities for data querying and manipulation in Vertica.