{ "cells": [ { "cell_type": "markdown", "id": "d48122c3", "metadata": {}, "source": [ "# DBLINK in VerticaPy" ] }, { "cell_type": "markdown", "id": "d5d649b7", "metadata": { "tags": [] }, "source": [ "## Introduction" ] }, { "cell_type": "markdown", "id": "5636992e", "metadata": {}, "source": [ "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.\n", "\n", "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:\n", "\n", "\n", " \n", "For more information about requirements, see the github repo.\n", "\n", "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. \n", "\n", "In the next section, let's work through an example of a database in PostgreSQL." ] }, { "cell_type": "markdown", "id": "434ab323", "metadata": { "tags": [] }, "source": [ "## Connecting to an External Database" ] }, { "cell_type": "code", "execution_count": 1, "id": "3a28e938", "metadata": {}, "outputs": [], "source": [ "# Importing VerticaPy\n", "import verticapy as vp" ] }, { "cell_type": "markdown", "id": "ec6ee24e", "metadata": {}, "source": [ "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. \n", "\n", "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." ] }, { "cell_type": "code", "execution_count": 2, "id": "3a52b566", "metadata": {}, "outputs": [], "source": [ "# Setting up a connection with a database with the alias \"pgdb\"\n", "vp.set_external_connection(cid = \"pgdb\",\n", " rowset = 500,\n", " symbol = \"&\")" ] }, { "cell_type": "markdown", "id": "a83ec7e8", "metadata": { "tags": [] }, "source": [ "## Creating a vDataFrame" ] }, { "cell_type": "markdown", "id": "b745b872", "metadata": {}, "source": [ "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. " ] }, { "cell_type": "code", "execution_count": 3, "id": "868c329c", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
Abc
IATA_CODE
Varchar(3)
Abc
Varchar(100)
Abc
CITY
Varchar(60)
Abc
STATE
Varchar(2)
Abc
COUNTRY
Varchar(100)
🌎
LATITUDE
Numeric(30)
🌎
LONGITUDE
Numeric(30)
1ABEAllentownPAUSA40.65236-75.4404
2ABIAbileneTXUSA32.41132-99.6819
3ABQAlbuquerqueNMUSA35.04022-106.60919
4ABRAberdeenSDUSA45.44906-98.42183
5ABYAlbanyGAUSA31.53552-84.19447
6ACKNantucketMAUSA41.25305-70.06018
7ACTWacoTXUSA31.61129-97.23052
8ACVArcata/EurekaCAUSA40.97812-124.10862
9ACYAtlantic CityNJUSA39.45758-74.57717
10ADKAdakAKUSA51.87796-176.64603
11ADQKodiakAKUSA57.74997-152.49386
12AEXAlexandriaLAUSA31.32737-92.54856
13AGSAugustaGAUSA33.36996-81.9645
14AKNKing SalmonAKUSA58.6768-156.64922
15ALBAlbanyNYUSA42.74812-73.80298
16ALOWaterlooIAUSA42.55708-92.40034
17AMAAmarilloTXUSA35.21937-101.70593
18ANCAnchorageAKUSA61.17432-149.99619
19APNAlpenaMIUSA45.07807-83.56029
20ASEAspenCOUSA39.22316-106.86885
21ATLAtlantaGAUSA33.64044-84.42694
22ATWAppletonWIUSA44.25741-88.51948
23AUSAustinTXUSA30.19453-97.66987
24AVLAshevilleNCUSA35.43619-82.54181
25AVPWilkes-Barre/ScrantonPAUSA41.33815-75.72427
26AZOKalamazooMIUSA42.23488-85.55206
27BDLWindsor LocksCTUSA41.93887-72.68323
28BETBethelAKUSA60.77978-161.838
29BFLBakersfieldCAUSA35.4336-119.05677
30BGMBinghamtonNYUSA42.20848-75.97961
31BGRBangorMEUSA44.80744-68.82814
32BHMBirminghamALUSA33.56294-86.75355
33BILBillingsMTUSA45.80766-108.54286
34BISBismarckNDUSA46.77411-100.74672
35BJIBemidjiMNUSA47.50942-94.93372
36BLIBellinghamWAUSA48.79275-122.53753
37BMIBloomingtonILUSA40.47799-88.91595
38BNANashvilleTNUSA36.12448-86.67818
39BOIBoiseIDUSA43.56444-116.22278
40BOSBostonMAUSA42.36435-71.00518
41BPTBeaumont/Port ArthurTXUSA29.95083-94.02069
42BQKBrunswickGAUSA31.25903-81.46631
43BQNAguadillaPRUSA18.49486-67.12944
44BRDBrainerdMNUSA46.39786-94.13723
45BROBrownsvilleTXUSA25.90683-97.42586
46BRWBarrowAKUSA71.28545-156.766
47BTMButteMTUSA45.9548-112.49746
48BTRBaton RougeLAUSA30.53316-91.14963
49BTVBurlingtonVTUSA44.473-73.15031
50BUFBuffaloNYUSA42.94052-78.73217
51BURBurbankCAUSA34.20062-118.3585
52BWIBaltimoreMDUSA39.1754-76.6682
53BZNBozemanMTUSA45.7769-111.15301
54CAEColumbiaSCUSA33.93884-81.11954
55CAKAkronOHUSA40.91631-81.44247
56CDCCedar CityUTUSA37.70097-113.09858
57CDVCordovaAKUSA60.49183-145.47765
58CECCrescent CityCAUSA41.78016-124.23653
59CHAChattanoogaTNUSA35.03527-85.20379
60CHOCharlottesvilleVAUSA38.13864-78.45286
61CHSCharlestonSCUSA32.89865-80.04051
62CIDCedar RapidsIAUSA41.88459-91.71087
63CIUSault Ste. MarieMIUSA46.25075-84.47239
64CLDSan DiegoCAUSA33.12723-117.27873
65CLEClevelandOHUSA41.41089-81.8494
66CLLCollege StationTXUSA30.58859-96.36382
67CLTCharlotteNCUSA35.21401-80.94313
68CMHColumbusOHUSA39.99799-82.89188
69CMIChampaign/UrbanaILUSA40.03925-88.27806
70CMXHancockMIUSA47.16842-88.48906
71CNYMoabUTUSA38.75496-109.75484
72CODCodyWYUSA44.52019-109.0238
73COSColorado SpringsCOUSA38.80581-104.70025
74COUColumbiaMOUSA38.81809-92.21963
75CPRCasperWYUSA42.90836-106.46447
76CRPCorpus ChristiTXUSA27.77036-97.50122
77CRWCharlestonWVUSA38.37315-81.59319
78CSGColumbusGAUSA32.51633-84.93886
79CVGCovingtonKYUSA39.04614-84.66217
80CWAMosineeWIUSA44.77762-89.66678
81DABDaytona BeachFLUSA29.17992-81.05806
82DALDallasTXUSA32.84711-96.85177
83DAYDaytonOHUSA39.90238-84.21938
84DBQDubuqueIAUSA42.40296-90.70917
85DCAArlingtonVAUSA38.85208-77.03772
86DENDenverCOUSA39.85841-104.667
87DFWDallas-Fort WorthTXUSA32.89595-97.0372
88DHNDothanALUSA31.32134-85.44963
89DIKDickinsonNDUSA46.79739-102.80195
90DLGDillinghamAKUSA59.04541-158.50334
91DLHDuluthMNUSA46.84209-92.19365
92DRODurangoCOUSA37.15152-107.75377
93DSMDes MoinesIAUSA41.53493-93.66068
94DTWDetroitMIUSA42.21206-83.34884
95DVLDevils LakeNDUSA48.11425-98.90878
96EAUEau ClaireWIUSA44.86526-91.48507
97ECPPanama CityFLUSA[null][null]
98EGEEagleCOUSA39.64257-106.9177
99EKOElkoNVUSA40.82493-115.7917
100ELMElmiraNYUSA42.15991-76.89144
Rows: 1-100 | Columns: 7
" ], "text/plain": [ " IATA_CODE AIRPORT CITY \\\\\n", "1 ABE Lehigh Valley International Airport Allentown \\\\\n", "2 ABI Abilene Regional Airport Abilene \\\\\n", "3 ABQ Albuquerque International Sunport Albuquerque \\\\\n", "4 ABR Aberdeen Regional Airport Aberdeen \\\\\n", "5 ABY Southwest Georgia Regional Airport Albany \\\\\n", "6 ACK Nantucket Memorial Airport Nantucket \\\\\n", "7 ACT Waco Regional Airport Waco \\\\\n", "8 ACV Arcata Airport Arcata/Eureka \\\\\n", "9 ACY Atlantic City International Airport Atlantic City \\\\\n", "10 ADK Adak Airport Adak \\\\\n", "11 ADQ Kodiak Airport Kodiak \\\\\n", "12 AEX Alexandria International Airport Alexandria \\\\\n", "13 AGS Augusta Regional Airport (Bush Field) Augusta \\\\\n", "14 AKN King Salmon Airport King Salmon \\\\\n", "15 ALB Albany International Airport Albany \\\\\n", "16 ALO Waterloo Regional Airport Waterloo \\\\\n", "17 AMA Rick Husband Amarillo International A... Amarillo \\\\\n", "18 ANC Ted Stevens Anchorage International A... Anchorage \\\\\n", "19 APN Alpena County Regional Airport Alpena \\\\\n", "20 ASE Aspen-Pitkin County Airport Aspen \\\\\n", "21 ATL Hartsfield-Jackson Atlanta Internatio... Atlanta \\\\\n", "22 ATW Appleton International Airport Appleton \\\\\n", "23 AUS Austin-Bergstrom International Airport Austin \\\\\n", "24 AVL Asheville Regional Airport Asheville \\\\\n", "25 AVP Wilkes-Barre/Scranton International A... Wilkes-Barre/Scranton \\\\\n", "26 AZO Kalamazoo/Battle Creek International ... Kalamazoo \\\\\n", "27 BDL Bradley International Airport Windsor Locks \\\\\n", "28 BET Bethel Airport Bethel \\\\\n", "29 BFL Meadows Field Bakersfield \\\\\n", "30 BGM Greater Binghamton Airport Binghamton \\\\\n", "31 BGR Bangor International Airport Bangor \\\\\n", "32 BHM Birmingham-Shuttlesworth Internationa... Birmingham \\\\\n", "33 BIL Billings Logan International Airport Billings \\\\\n", "34 BIS Bismarck Municipal Airport Bismarck \\\\\n", "35 BJI Bemidji Regional Airport Bemidji \\\\\n", "36 BLI Bellingham International Airport Bellingham \\\\\n", "37 BMI Central Illinois Regional Airport at ... Bloomington \\\\\n", "38 BNA Nashville International Airport Nashville \\\\\n", "39 BOI Boise Airport (Boise Air Terminal) Boise \\\\\n", "40 BOS Gen. Edward Lawrence Logan Internatio... Boston \\\\\n", "41 BPT Jack Brooks Regional Airport (Southea... Beaumont/Port Arthur \\\\\n", "42 BQK Brunswick Golden Isles Airport Brunswick \\\\\n", "43 BQN Rafael Hernández Airport Aguadilla \\\\\n", "44 BRD Brainerd Lakes Regional Airport Brainerd \\\\\n", "45 BRO Brownsville/South Padre Island Intern... Brownsville \\\\\n", "46 BRW Wiley Post-Will Rogers Memorial Airport Barrow \\\\\n", "47 BTM Bert Mooney Airport Butte \\\\\n", "48 BTR Baton Rouge Metropolitan Airport Baton Rouge \\\\\n", "49 BTV Burlington International Airport Burlington \\\\\n", "50 BUF Buffalo Niagara International Airport Buffalo \\\\\n", "51 BUR Bob Hope Airport (Hollywood Burbank A... Burbank \\\\\n", "52 BWI Baltimore-Washington International Ai... Baltimore \\\\\n", "53 BZN Bozeman Yellowstone International Air... Bozeman \\\\\n", "54 CAE Columbia Metropolitan Airport Columbia \\\\\n", "55 CAK Akron-Canton Regional Airport Akron \\\\\n", "56 CDC Cedar City Regional Airport Cedar City \\\\\n", "57 CDV Merle K. (Mudhole) Smith Airport Cordova \\\\\n", "58 CEC Del Norte County Airport (Jack McNama... Crescent City \\\\\n", "59 CHA Chattanooga Metropolitan Airport (Lov... Chattanooga \\\\\n", "60 CHO Charlottesville-Albemarle Airport Charlottesville \\\\\n", "61 CHS Charleston International Airport/Char... Charleston \\\\\n", "62 CID The Eastern Iowa Airport Cedar Rapids \\\\\n", "63 CIU Chippewa County International Airport Sault Ste. Marie \\\\\n", "64 CLD McClellan-Palomar Airport San Diego \\\\\n", "65 CLE Cleveland Hopkins International Airport Cleveland \\\\\n", "66 CLL Easterwood Airport College Station \\\\\n", "67 CLT Charlotte Douglas International Airport Charlotte \\\\\n", "68 CMH Port Columbus International Airport Columbus \\\\\n", "69 CMI University of Illinois - Willard Airport Champaign/Urbana \\\\\n", "70 CMX Houghton County Memorial Airport Hancock \\\\\n", "71 CNY Canyonlands Field Moab \\\\\n", "72 COD Yellowstone Regional Airport Cody \\\\\n", "73 COS City of Colorado Springs Municipal Ai... Colorado Springs \\\\\n", "74 COU Columbia Regional Airport Columbia \\\\\n", "75 CPR Natrona County International Airport Casper \\\\\n", "76 CRP Corpus Christi International Airport Corpus Christi \\\\\n", "77 CRW Yeager Airport Charleston \\\\\n", "78 CSG Columbus Metropolitan Airport Columbus \\\\\n", "79 CVG Cincinnati/Northern Kentucky Internat... Covington \\\\\n", "80 CWA Central Wisconsin Airport Mosinee \\\\\n", "81 DAB Daytona Beach International Airport Daytona Beach \\\\\n", "82 DAL Dallas Love Field Dallas \\\\\n", "83 DAY James M. Cox Dayton International Air... Dayton \\\\\n", "84 DBQ Dubuque Regional Airport Dubuque \\\\\n", "85 DCA Ronald Reagan Washington National Air... Arlington \\\\\n", "86 DEN Denver International Airport Denver \\\\\n", "87 DFW Dallas/Fort Worth International Airport Dallas-Fort Worth \\\\\n", "88 DHN Dothan Regional Airport Dothan \\\\\n", "89 DIK Dickinson Theodore Roosevelt Regional... Dickinson \\\\\n", "90 DLG Dillingham Airport Dillingham \\\\\n", "91 DLH Duluth International Airport Duluth \\\\\n", "92 DRO Durango-La Plata County Airport Durango \\\\\n", "93 DSM Des Moines International Airport Des Moines \\\\\n", "94 DTW Detroit Metropolitan Airport Detroit \\\\\n", "95 DVL Devils Lake Regional Airport Devils Lake \\\\\n", "96 EAU Chippewa Valley Regional Airport Eau Claire \\\\\n", "97 ECP Northwest Florida Beaches Internation... Panama City \\\\\n", "98 EGE Eagle County Regional Airport Eagle \\\\\n", "99 EKO Elko Regional Airport Elko \\\\\n", "100 ELM Elmira/Corning Regional Airport Elmira \\\\\n", " STATE COUNTRY LATITUDE LONGITUDE \n", "1 PA USA 40.65236 -75.4404 \n", "2 TX USA 32.41132 -99.6819 \n", "3 NM USA 35.04022 -106.60919 \n", "4 SD USA 45.44906 -98.42183 \n", "5 GA USA 31.53552 -84.19447 \n", "6 MA USA 41.25305 -70.06018 \n", "7 TX USA 31.61129 -97.23052 \n", "8 CA USA 40.97812 -124.10862 \n", "9 NJ USA 39.45758 -74.57717 \n", "10 AK USA 51.87796 -176.64603 \n", "11 AK USA 57.74997 -152.49386 \n", "12 LA USA 31.32737 -92.54856 \n", "13 GA USA 33.36996 -81.9645 \n", "14 AK USA 58.6768 -156.64922 \n", "15 NY USA 42.74812 -73.80298 \n", "16 IA USA 42.55708 -92.40034 \n", "17 TX USA 35.21937 -101.70593 \n", "18 AK USA 61.17432 -149.99619 \n", "19 MI USA 45.07807 -83.56029 \n", "20 CO USA 39.22316 -106.86885 \n", "21 GA USA 33.64044 -84.42694 \n", "22 WI USA 44.25741 -88.51948 \n", "23 TX USA 30.19453 -97.66987 \n", "24 NC USA 35.43619 -82.54181 \n", "25 PA USA 41.33815 -75.72427 \n", "26 MI USA 42.23488 -85.55206 \n", "27 CT USA 41.93887 -72.68323 \n", "28 AK USA 60.77978 -161.838 \n", "29 CA USA 35.4336 -119.05677 \n", "30 NY USA 42.20848 -75.97961 \n", "31 ME USA 44.80744 -68.82814 \n", "32 AL USA 33.56294 -86.75355 \n", "33 MT USA 45.80766 -108.54286 \n", "34 ND USA 46.77411 -100.74672 \n", "35 MN USA 47.50942 -94.93372 \n", "36 WA USA 48.79275 -122.53753 \n", "37 IL USA 40.47799 -88.91595 \n", "38 TN USA 36.12448 -86.67818 \n", "39 ID USA 43.56444 -116.22278 \n", "40 MA USA 42.36435 -71.00518 \n", "41 TX USA 29.95083 -94.02069 \n", "42 GA USA 31.25903 -81.46631 \n", "43 PR USA 18.49486 -67.12944 \n", "44 MN USA 46.39786 -94.13723 \n", "45 TX USA 25.90683 -97.42586 \n", "46 AK USA 71.28545 -156.766 \n", "47 MT USA 45.9548 -112.49746 \n", "48 LA USA 30.53316 -91.14963 \n", "49 VT USA 44.473 -73.15031 \n", "50 NY USA 42.94052 -78.73217 \n", "51 CA USA 34.20062 -118.3585 \n", "52 MD USA 39.1754 -76.6682 \n", "53 MT USA 45.7769 -111.15301 \n", "54 SC USA 33.93884 -81.11954 \n", "55 OH USA 40.91631 -81.44247 \n", "56 UT USA 37.70097 -113.09858 \n", "57 AK USA 60.49183 -145.47765 \n", "58 CA USA 41.78016 -124.23653 \n", "59 TN USA 35.03527 -85.20379 \n", "60 VA USA 38.13864 -78.45286 \n", "61 SC USA 32.89865 -80.04051 \n", "62 IA USA 41.88459 -91.71087 \n", "63 MI USA 46.25075 -84.47239 \n", "64 CA USA 33.12723 -117.27873 \n", "65 OH USA 41.41089 -81.8494 \n", "66 TX USA 30.58859 -96.36382 \n", "67 NC USA 35.21401 -80.94313 \n", "68 OH USA 39.99799 -82.89188 \n", "69 IL USA 40.03925 -88.27806 \n", "70 MI USA 47.16842 -88.48906 \n", "71 UT USA 38.75496 -109.75484 \n", "72 WY USA 44.52019 -109.0238 \n", "73 CO USA 38.80581 -104.70025 \n", "74 MO USA 38.81809 -92.21963 \n", "75 WY USA 42.90836 -106.46447 \n", "76 TX USA 27.77036 -97.50122 \n", "77 WV USA 38.37315 -81.59319 \n", "78 GA USA 32.51633 -84.93886 \n", "79 KY USA 39.04614 -84.66217 \n", "80 WI USA 44.77762 -89.66678 \n", "81 FL USA 29.17992 -81.05806 \n", "82 TX USA 32.84711 -96.85177 \n", "83 OH USA 39.90238 -84.21938 \n", "84 IA USA 42.40296 -90.70917 \n", "85 VA USA 38.85208 -77.03772 \n", "86 CO USA 39.85841 -104.667 \n", "87 TX USA 32.89595 -97.0372 \n", "88 AL USA 31.32134 -85.44963 \n", "89 ND USA 46.79739 -102.80195 \n", "90 AK USA 59.04541 -158.50334 \n", "91 MN USA 46.84209 -92.19365 \n", "92 CO USA 37.15152 -107.75377 \n", "93 IA USA 41.53493 -93.66068 \n", "94 MI USA 42.21206 -83.34884 \n", "95 ND USA 48.11425 -98.90878 \n", "96 WI USA 44.86526 -91.48507 \n", "97 FL USA None None \n", "98 CO USA 39.64257 -106.9177 \n", "99 NV USA 40.82493 -115.7917 \n", "100 NY USA 42.15991 -76.89144 \n", "Rows: 1-100 | Columns: 7" ] }, "execution_count": 3, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Creating a vDataFrame using an SQL query by setting external = True.\n", "tab_data = vp.vDataFrame(input_relation = \"airports\",\n", " external = True,\n", " symbol = \"&\")\n", "tab_data" ] }, { "cell_type": "markdown", "id": "cbc86945", "metadata": {}, "source": [ "All vDataFrame functions are available for this imported table. For example, we can get all the column names:" ] }, { "cell_type": "code", "execution_count": null, "id": "8b412ed1", "metadata": {}, "outputs": [], "source": [ "# Get all columns of the dataset\n", "tab_data.get_columns()" ] }, { "cell_type": "markdown", "id": "1fdd777f", "metadata": {}, "source": [ "Or the column data types:" ] }, { "cell_type": "code", "execution_count": null, "id": "336ee5b9", "metadata": {}, "outputs": [], "source": [ "# Get data types of all columns inside the dataset\n", "tab_data.dtypes()" ] }, { "cell_type": "markdown", "id": "ae44ba95", "metadata": {}, "source": [ "Or the count of the datapoints:" ] }, { "cell_type": "code", "execution_count": 6, "id": "30713031", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
count
"IATA_CODE"322.0
"AIRPORT"322.0
"CITY"322.0
"STATE"322.0
"COUNTRY"322.0
"LATITUDE"319.0
"LONGITUDE"319.0
Rows: 1-7 | Columns: 2
" ], "text/plain": [ " count \n", "\"IATA_CODE\" 322.0 \n", "\"AIRPORT\" 322.0 \n", "\"CITY\" 322.0 \n", "\"STATE\" 322.0 \n", "\"COUNTRY\" 322.0 \n", "\"LATITUDE\" 319.0 \n", "\"LONGITUDE\" 319.0 \n", "Rows: 1-7 | Columns: 2" ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Counting all elements inside each column\n", "tab_data.count()" ] }, { "cell_type": "markdown", "id": "4a746968", "metadata": {}, "source": [ "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." ] }, { "cell_type": "code", "execution_count": 7, "id": "4417c23d", "metadata": { "tags": [] }, "outputs": [ { "data": { "text/html": [ "
Abc
IATA_CODE
Varchar(3)
Abc
Varchar(100)
Abc
CITY
Varchar(60)
Abc
STATE
Varchar(2)
Abc
COUNTRY
Varchar(100)
🌎
LATITUDE
Numeric(30)
🌎
LONGITUDE
Numeric(30)
1ABEAllentownPAUSA40.65236-75.4404
2ABIAbileneTXUSA32.41132-99.6819
3ABQAlbuquerqueNMUSA35.04022-106.60919
4ABRAberdeenSDUSA45.44906-98.42183
5ABYAlbanyGAUSA31.53552-84.19447
6ACKNantucketMAUSA41.25305-70.06018
7ACTWacoTXUSA31.61129-97.23052
8ACVArcata/EurekaCAUSA40.97812-124.10862
9ACYAtlantic CityNJUSA39.45758-74.57717
10ADKAdakAKUSA51.87796-176.64603
11ADQKodiakAKUSA57.74997-152.49386
12AEXAlexandriaLAUSA31.32737-92.54856
13AGSAugustaGAUSA33.36996-81.9645
14AKNKing SalmonAKUSA58.6768-156.64922
15ALBAlbanyNYUSA42.74812-73.80298
16ALOWaterlooIAUSA42.55708-92.40034
17AMAAmarilloTXUSA35.21937-101.70593
18ANCAnchorageAKUSA61.17432-149.99619
19APNAlpenaMIUSA45.07807-83.56029
20ASEAspenCOUSA39.22316-106.86885
21ATLAtlantaGAUSA33.64044-84.42694
22ATWAppletonWIUSA44.25741-88.51948
23AUSAustinTXUSA30.19453-97.66987
24AVLAshevilleNCUSA35.43619-82.54181
25AVPWilkes-Barre/ScrantonPAUSA41.33815-75.72427
26AZOKalamazooMIUSA42.23488-85.55206
27BDLWindsor LocksCTUSA41.93887-72.68323
28BETBethelAKUSA60.77978-161.838
29BFLBakersfieldCAUSA35.4336-119.05677
30BGMBinghamtonNYUSA42.20848-75.97961
31BGRBangorMEUSA44.80744-68.82814
32BHMBirminghamALUSA33.56294-86.75355
33BILBillingsMTUSA45.80766-108.54286
34BISBismarckNDUSA46.77411-100.74672
35BJIBemidjiMNUSA47.50942-94.93372
36BLIBellinghamWAUSA48.79275-122.53753
37BMIBloomingtonILUSA40.47799-88.91595
38BNANashvilleTNUSA36.12448-86.67818
39BOIBoiseIDUSA43.56444-116.22278
40BOSBostonMAUSA42.36435-71.00518
41BPTBeaumont/Port ArthurTXUSA29.95083-94.02069
42BQKBrunswickGAUSA31.25903-81.46631
43BQNAguadillaPRUSA18.49486-67.12944
44BRDBrainerdMNUSA46.39786-94.13723
45BROBrownsvilleTXUSA25.90683-97.42586
46BRWBarrowAKUSA71.28545-156.766
47BTMButteMTUSA45.9548-112.49746
48BTRBaton RougeLAUSA30.53316-91.14963
49BTVBurlingtonVTUSA44.473-73.15031
50BUFBuffaloNYUSA42.94052-78.73217
51BURBurbankCAUSA34.20062-118.3585
52BWIBaltimoreMDUSA39.1754-76.6682
53BZNBozemanMTUSA45.7769-111.15301
54CAEColumbiaSCUSA33.93884-81.11954
55CAKAkronOHUSA40.91631-81.44247
56CDCCedar CityUTUSA37.70097-113.09858
57CDVCordovaAKUSA60.49183-145.47765
58CECCrescent CityCAUSA41.78016-124.23653
59CHAChattanoogaTNUSA35.03527-85.20379
60CHOCharlottesvilleVAUSA38.13864-78.45286
61CHSCharlestonSCUSA32.89865-80.04051
62CIDCedar RapidsIAUSA41.88459-91.71087
63CIUSault Ste. MarieMIUSA46.25075-84.47239
64CLDSan DiegoCAUSA33.12723-117.27873
65CLEClevelandOHUSA41.41089-81.8494
66CLLCollege StationTXUSA30.58859-96.36382
67CLTCharlotteNCUSA35.21401-80.94313
68CMHColumbusOHUSA39.99799-82.89188
69CMIChampaign/UrbanaILUSA40.03925-88.27806
70CMXHancockMIUSA47.16842-88.48906
71CNYMoabUTUSA38.75496-109.75484
72CODCodyWYUSA44.52019-109.0238
73COSColorado SpringsCOUSA38.80581-104.70025
74COUColumbiaMOUSA38.81809-92.21963
75CPRCasperWYUSA42.90836-106.46447
76CRPCorpus ChristiTXUSA27.77036-97.50122
77CRWCharlestonWVUSA38.37315-81.59319
78CSGColumbusGAUSA32.51633-84.93886
79CVGCovingtonKYUSA39.04614-84.66217
80CWAMosineeWIUSA44.77762-89.66678
81DABDaytona BeachFLUSA29.17992-81.05806
82DALDallasTXUSA32.84711-96.85177
83DAYDaytonOHUSA39.90238-84.21938
84DBQDubuqueIAUSA42.40296-90.70917
85DCAArlingtonVAUSA38.85208-77.03772
86DENDenverCOUSA39.85841-104.667
87DFWDallas-Fort WorthTXUSA32.89595-97.0372
88DHNDothanALUSA31.32134-85.44963
89DIKDickinsonNDUSA46.79739-102.80195
90DLGDillinghamAKUSA59.04541-158.50334
91DLHDuluthMNUSA46.84209-92.19365
92DRODurangoCOUSA37.15152-107.75377
93DSMDes MoinesIAUSA41.53493-93.66068
94DTWDetroitMIUSA42.21206-83.34884
95DVLDevils LakeNDUSA48.11425-98.90878
96EAUEau ClaireWIUSA44.86526-91.48507
97ECPPanama CityFLUSA[null][null]
98EGEEagleCOUSA39.64257-106.9177
99EKOElkoNVUSA40.82493-115.7917
100ELMElmiraNYUSA42.15991-76.89144
Rows: 1-100 | Columns: 7
" ], "text/plain": [ " IATA_CODE AIRPORT CITY \\\\\n", "1 ABE Lehigh Valley International Airport Allentown \\\\\n", "2 ABI Abilene Regional Airport Abilene \\\\\n", "3 ABQ Albuquerque International Sunport Albuquerque \\\\\n", "4 ABR Aberdeen Regional Airport Aberdeen \\\\\n", "5 ABY Southwest Georgia Regional Airport Albany \\\\\n", "6 ACK Nantucket Memorial Airport Nantucket \\\\\n", "7 ACT Waco Regional Airport Waco \\\\\n", "8 ACV Arcata Airport Arcata/Eureka \\\\\n", "9 ACY Atlantic City International Airport Atlantic City \\\\\n", "10 ADK Adak Airport Adak \\\\\n", "11 ADQ Kodiak Airport Kodiak \\\\\n", "12 AEX Alexandria International Airport Alexandria \\\\\n", "13 AGS Augusta Regional Airport (Bush Field) Augusta \\\\\n", "14 AKN King Salmon Airport King Salmon \\\\\n", "15 ALB Albany International Airport Albany \\\\\n", "16 ALO Waterloo Regional Airport Waterloo \\\\\n", "17 AMA Rick Husband Amarillo International A... Amarillo \\\\\n", "18 ANC Ted Stevens Anchorage International A... Anchorage \\\\\n", "19 APN Alpena County Regional Airport Alpena \\\\\n", "20 ASE Aspen-Pitkin County Airport Aspen \\\\\n", "21 ATL Hartsfield-Jackson Atlanta Internatio... Atlanta \\\\\n", "22 ATW Appleton International Airport Appleton \\\\\n", "23 AUS Austin-Bergstrom International Airport Austin \\\\\n", "24 AVL Asheville Regional Airport Asheville \\\\\n", "25 AVP Wilkes-Barre/Scranton International A... Wilkes-Barre/Scranton \\\\\n", "26 AZO Kalamazoo/Battle Creek International ... Kalamazoo \\\\\n", "27 BDL Bradley International Airport Windsor Locks \\\\\n", "28 BET Bethel Airport Bethel \\\\\n", "29 BFL Meadows Field Bakersfield \\\\\n", "30 BGM Greater Binghamton Airport Binghamton \\\\\n", "31 BGR Bangor International Airport Bangor \\\\\n", "32 BHM Birmingham-Shuttlesworth Internationa... Birmingham \\\\\n", "33 BIL Billings Logan International Airport Billings \\\\\n", "34 BIS Bismarck Municipal Airport Bismarck \\\\\n", "35 BJI Bemidji Regional Airport Bemidji \\\\\n", "36 BLI Bellingham International Airport Bellingham \\\\\n", "37 BMI Central Illinois Regional Airport at ... Bloomington \\\\\n", "38 BNA Nashville International Airport Nashville \\\\\n", "39 BOI Boise Airport (Boise Air Terminal) Boise \\\\\n", "40 BOS Gen. Edward Lawrence Logan Internatio... Boston \\\\\n", "41 BPT Jack Brooks Regional Airport (Southea... Beaumont/Port Arthur \\\\\n", "42 BQK Brunswick Golden Isles Airport Brunswick \\\\\n", "43 BQN Rafael Hernández Airport Aguadilla \\\\\n", "44 BRD Brainerd Lakes Regional Airport Brainerd \\\\\n", "45 BRO Brownsville/South Padre Island Intern... Brownsville \\\\\n", "46 BRW Wiley Post-Will Rogers Memorial Airport Barrow \\\\\n", "47 BTM Bert Mooney Airport Butte \\\\\n", "48 BTR Baton Rouge Metropolitan Airport Baton Rouge \\\\\n", "49 BTV Burlington International Airport Burlington \\\\\n", "50 BUF Buffalo Niagara International Airport Buffalo \\\\\n", "51 BUR Bob Hope Airport (Hollywood Burbank A... Burbank \\\\\n", "52 BWI Baltimore-Washington International Ai... Baltimore \\\\\n", "53 BZN Bozeman Yellowstone International Air... Bozeman \\\\\n", "54 CAE Columbia Metropolitan Airport Columbia \\\\\n", "55 CAK Akron-Canton Regional Airport Akron \\\\\n", "56 CDC Cedar City Regional Airport Cedar City \\\\\n", "57 CDV Merle K. (Mudhole) Smith Airport Cordova \\\\\n", "58 CEC Del Norte County Airport (Jack McNama... Crescent City \\\\\n", "59 CHA Chattanooga Metropolitan Airport (Lov... Chattanooga \\\\\n", "60 CHO Charlottesville-Albemarle Airport Charlottesville \\\\\n", "61 CHS Charleston International Airport/Char... Charleston \\\\\n", "62 CID The Eastern Iowa Airport Cedar Rapids \\\\\n", "63 CIU Chippewa County International Airport Sault Ste. Marie \\\\\n", "64 CLD McClellan-Palomar Airport San Diego \\\\\n", "65 CLE Cleveland Hopkins International Airport Cleveland \\\\\n", "66 CLL Easterwood Airport College Station \\\\\n", "67 CLT Charlotte Douglas International Airport Charlotte \\\\\n", "68 CMH Port Columbus International Airport Columbus \\\\\n", "69 CMI University of Illinois - Willard Airport Champaign/Urbana \\\\\n", "70 CMX Houghton County Memorial Airport Hancock \\\\\n", "71 CNY Canyonlands Field Moab \\\\\n", "72 COD Yellowstone Regional Airport Cody \\\\\n", "73 COS City of Colorado Springs Municipal Ai... Colorado Springs \\\\\n", "74 COU Columbia Regional Airport Columbia \\\\\n", "75 CPR Natrona County International Airport Casper \\\\\n", "76 CRP Corpus Christi International Airport Corpus Christi \\\\\n", "77 CRW Yeager Airport Charleston \\\\\n", "78 CSG Columbus Metropolitan Airport Columbus \\\\\n", "79 CVG Cincinnati/Northern Kentucky Internat... Covington \\\\\n", "80 CWA Central Wisconsin Airport Mosinee \\\\\n", "81 DAB Daytona Beach International Airport Daytona Beach \\\\\n", "82 DAL Dallas Love Field Dallas \\\\\n", "83 DAY James M. Cox Dayton International Air... Dayton \\\\\n", "84 DBQ Dubuque Regional Airport Dubuque \\\\\n", "85 DCA Ronald Reagan Washington National Air... Arlington \\\\\n", "86 DEN Denver International Airport Denver \\\\\n", "87 DFW Dallas/Fort Worth International Airport Dallas-Fort Worth \\\\\n", "88 DHN Dothan Regional Airport Dothan \\\\\n", "89 DIK Dickinson Theodore Roosevelt Regional... Dickinson \\\\\n", "90 DLG Dillingham Airport Dillingham \\\\\n", "91 DLH Duluth International Airport Duluth \\\\\n", "92 DRO Durango-La Plata County Airport Durango \\\\\n", "93 DSM Des Moines International Airport Des Moines \\\\\n", "94 DTW Detroit Metropolitan Airport Detroit \\\\\n", "95 DVL Devils Lake Regional Airport Devils Lake \\\\\n", "96 EAU Chippewa Valley Regional Airport Eau Claire \\\\\n", "97 ECP Northwest Florida Beaches Internation... Panama City \\\\\n", "98 EGE Eagle County Regional Airport Eagle \\\\\n", "99 EKO Elko Regional Airport Elko \\\\\n", "100 ELM Elmira/Corning Regional Airport Elmira \\\\\n", " STATE COUNTRY LATITUDE LONGITUDE \n", "1 PA USA 40.65236 -75.4404 \n", "2 TX USA 32.41132 -99.6819 \n", "3 NM USA 35.04022 -106.60919 \n", "4 SD USA 45.44906 -98.42183 \n", "5 GA USA 31.53552 -84.19447 \n", "6 MA USA 41.25305 -70.06018 \n", "7 TX USA 31.61129 -97.23052 \n", "8 CA USA 40.97812 -124.10862 \n", "9 NJ USA 39.45758 -74.57717 \n", "10 AK USA 51.87796 -176.64603 \n", "11 AK USA 57.74997 -152.49386 \n", "12 LA USA 31.32737 -92.54856 \n", "13 GA USA 33.36996 -81.9645 \n", "14 AK USA 58.6768 -156.64922 \n", "15 NY USA 42.74812 -73.80298 \n", "16 IA USA 42.55708 -92.40034 \n", "17 TX USA 35.21937 -101.70593 \n", "18 AK USA 61.17432 -149.99619 \n", "19 MI USA 45.07807 -83.56029 \n", "20 CO USA 39.22316 -106.86885 \n", "21 GA USA 33.64044 -84.42694 \n", "22 WI USA 44.25741 -88.51948 \n", "23 TX USA 30.19453 -97.66987 \n", "24 NC USA 35.43619 -82.54181 \n", "25 PA USA 41.33815 -75.72427 \n", "26 MI USA 42.23488 -85.55206 \n", "27 CT USA 41.93887 -72.68323 \n", "28 AK USA 60.77978 -161.838 \n", "29 CA USA 35.4336 -119.05677 \n", "30 NY USA 42.20848 -75.97961 \n", "31 ME USA 44.80744 -68.82814 \n", "32 AL USA 33.56294 -86.75355 \n", "33 MT USA 45.80766 -108.54286 \n", "34 ND USA 46.77411 -100.74672 \n", "35 MN USA 47.50942 -94.93372 \n", "36 WA USA 48.79275 -122.53753 \n", "37 IL USA 40.47799 -88.91595 \n", "38 TN USA 36.12448 -86.67818 \n", "39 ID USA 43.56444 -116.22278 \n", "40 MA USA 42.36435 -71.00518 \n", "41 TX USA 29.95083 -94.02069 \n", "42 GA USA 31.25903 -81.46631 \n", "43 PR USA 18.49486 -67.12944 \n", "44 MN USA 46.39786 -94.13723 \n", "45 TX USA 25.90683 -97.42586 \n", "46 AK USA 71.28545 -156.766 \n", "47 MT USA 45.9548 -112.49746 \n", "48 LA USA 30.53316 -91.14963 \n", "49 VT USA 44.473 -73.15031 \n", "50 NY USA 42.94052 -78.73217 \n", "51 CA USA 34.20062 -118.3585 \n", "52 MD USA 39.1754 -76.6682 \n", "53 MT USA 45.7769 -111.15301 \n", "54 SC USA 33.93884 -81.11954 \n", "55 OH USA 40.91631 -81.44247 \n", "56 UT USA 37.70097 -113.09858 \n", "57 AK USA 60.49183 -145.47765 \n", "58 CA USA 41.78016 -124.23653 \n", "59 TN USA 35.03527 -85.20379 \n", "60 VA USA 38.13864 -78.45286 \n", "61 SC USA 32.89865 -80.04051 \n", "62 IA USA 41.88459 -91.71087 \n", "63 MI USA 46.25075 -84.47239 \n", "64 CA USA 33.12723 -117.27873 \n", "65 OH USA 41.41089 -81.8494 \n", "66 TX USA 30.58859 -96.36382 \n", "67 NC USA 35.21401 -80.94313 \n", "68 OH USA 39.99799 -82.89188 \n", "69 IL USA 40.03925 -88.27806 \n", "70 MI USA 47.16842 -88.48906 \n", "71 UT USA 38.75496 -109.75484 \n", "72 WY USA 44.52019 -109.0238 \n", "73 CO USA 38.80581 -104.70025 \n", "74 MO USA 38.81809 -92.21963 \n", "75 WY USA 42.90836 -106.46447 \n", "76 TX USA 27.77036 -97.50122 \n", "77 WV USA 38.37315 -81.59319 \n", "78 GA USA 32.51633 -84.93886 \n", "79 KY USA 39.04614 -84.66217 \n", "80 WI USA 44.77762 -89.66678 \n", "81 FL USA 29.17992 -81.05806 \n", "82 TX USA 32.84711 -96.85177 \n", "83 OH USA 39.90238 -84.21938 \n", "84 IA USA 42.40296 -90.70917 \n", "85 VA USA 38.85208 -77.03772 \n", "86 CO USA 39.85841 -104.667 \n", "87 TX USA 32.89595 -97.0372 \n", "88 AL USA 31.32134 -85.44963 \n", "89 ND USA 46.79739 -102.80195 \n", "90 AK USA 59.04541 -158.50334 \n", "91 MN USA 46.84209 -92.19365 \n", "92 CO USA 37.15152 -107.75377 \n", "93 IA USA 41.53493 -93.66068 \n", "94 MI USA 42.21206 -83.34884 \n", "95 ND USA 48.11425 -98.90878 \n", "96 WI USA 44.86526 -91.48507 \n", "97 FL USA None None \n", "98 CO USA 39.64257 -106.9177 \n", "99 NV USA 40.82493 -115.7917 \n", "100 NY USA 42.15991 -76.89144 \n", "Rows: 1-100 | Columns: 7" ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Creating a vDataFrame and setting sql_push_ext to True, which tries \n", "# to push SQL queries to external database (where possible).\n", "Ext_Table=vp.vDataFrame(input_relation = \"airports\",\n", " external = True,\n", " symbol = \"&\",\n", " sql_push_ext = True)\n", "Ext_Table" ] }, { "cell_type": "markdown", "id": "f2121deb", "metadata": {}, "source": [ "If we look at the SQL generated in background, we can see that it pushes the aggregation query to the database." ] }, { "cell_type": "code", "execution_count": 8, "id": "2ee19308", "metadata": {}, "outputs": [], "source": [ "# Turning on SQL output to view the queries\n", "vp.set_option(\"sql_on\",True)" ] }, { "cell_type": "markdown", "id": "dbebc5cf", "metadata": {}, "source": [ "Let's look at the count query again, and see how VerticaPy is pushing it to the external database." ] }, { "cell_type": "code", "execution_count": 9, "id": "66511256", "metadata": {}, "outputs": [ { "data": { "text/html": [ "

Computing the different aggregations.

" ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "   SELECT
    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 ()" ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "
count
"IATA_CODE"322.0
"AIRPORT"322.0
"CITY"322.0
"STATE"322.0
"COUNTRY"322.0
"LATITUDE"319.0
"LONGITUDE"319.0
Rows: 1-7 | Columns: 2
" ], "text/plain": [ " count \n", "\"IATA_CODE\" 322.0 \n", "\"AIRPORT\" 322.0 \n", "\"CITY\" 322.0 \n", "\"STATE\" 322.0 \n", "\"COUNTRY\" 322.0 \n", "\"LATITUDE\" 319.0 \n", "\"LONGITUDE\" 319.0 \n", "Rows: 1-7 | Columns: 2" ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Counting elements in each column\n", "Ext_Table.count()" ] }, { "cell_type": "markdown", "id": "85ae5c0b", "metadata": {}, "source": [ "Let's also look at the \"min\" method:" ] }, { "cell_type": "code", "execution_count": 10, "id": "cef07ecb", "metadata": {}, "outputs": [ { "data": { "text/html": [ "

Computing the different aggregations.

" ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "   SELECT
    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 ()" ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/plain": [ "13.48345" ] }, "execution_count": 10, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Finding minimum in the ID column of Ext_Table\n", "Ext_Table[\"LATITUDE\"].min()" ] }, { "cell_type": "markdown", "id": "59cda922", "metadata": {}, "source": [ "For the above examples, the queries were pushed to the external database.\n", "

\n", "If the function is unique to Vertica, it automatically fetches the data from the external database to compute on the Vertica server. \n", " \n", "Let's try an example with the describe function, which is a unique Vertica function.\n", "

" ] }, { "cell_type": "code", "execution_count": 11, "id": "54d93dd9", "metadata": {}, "outputs": [ { "data": { "text/html": [ "

Getting the version.

" ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "   SELECT
    /*+LABEL('utilities.version')*/ version()" ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "

Computing the descriptive statistics of all numerical columns using SUMMARIZE_NUMCOL.

" ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "   SELECT
    /*+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" ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "
count
mean
std
min
approx_25%
approx_50%
approx_75%
max
"LATITUDE"31938.98124391849538.6167355810180413.4834533.6520439.2976143.15467571.28545
"LONGITUDE"319-98.378964451410721.5234920464981-176.64603-110.839385-93.40307-82.722995-64.79856
Rows: 1-2 | Columns: 9
" ], "text/plain": [ " count mean std min \\\\\n", "\"LATITUDE\" 319 38.9812439184953 8.61673558101804 13.48345 \\\\\n", "\"LONGITUDE\" 319 -98.3789644514107 21.5234920464981 -176.64603 \\\\\n", " approx_25% approx_50% approx_75% max \n", "\"LATITUDE\" 33.65204 39.29761 43.154675 71.28545 \n", "\"LONGITUDE\" -110.839385 -93.40307 -82.722995 -64.79856 \n", "Rows: 1-2 | Columns: 9" ] }, "execution_count": 11, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Describe the main attributes of numerical columns in the Ext_table\n", "Ext_Table.describe()" ] }, { "cell_type": "markdown", "id": "b1323aea", "metadata": {}, "source": [ "We can see that the data was fetched from the external database to be computed in Vertica.\n", "\n", "(Now we can turn off SQL display)." ] }, { "cell_type": "code", "execution_count": 12, "id": "1ae6add9", "metadata": {}, "outputs": [], "source": [ "# Turning off SQL display\n", "vp.set_option(\"sql_on\",\n", " False)" ] }, { "cell_type": "markdown", "id": "05d36eec", "metadata": { "tags": [] }, "source": [ "## Using SQL Magic Cells" ] }, { "cell_type": "code", "execution_count": 2, "id": "636d7f21", "metadata": {}, "outputs": [], "source": [ "# Load extension for running SQL magic cells\n", "%load_ext verticapy.sql" ] }, { "cell_type": "markdown", "id": "161ba25d", "metadata": {}, "source": [ "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.\n", "\n", "This makes writing queries a lot more convenient and visually appealing!\n", "\n", "Now we will try to get fetch data from our external database \"pgdb, whose special character is \"&\"." ] }, { "cell_type": "code", "execution_count": 14, "id": "2eb392f1", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
Execution: 0.106s
" ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "
Abc
IATA_CODE
Varchar(3)
Abc
Varchar(100)
Abc
CITY
Varchar(60)
Abc
STATE
Varchar(2)
Abc
COUNTRY
Varchar(100)
🌎
LATITUDE
Numeric(30)
🌎
LONGITUDE
Numeric(30)
1ABEAllentownPAUSA40.65236-75.4404
2ABIAbileneTXUSA32.41132-99.6819
3ABQAlbuquerqueNMUSA35.04022-106.60919
4ABRAberdeenSDUSA45.44906-98.42183
5ABYAlbanyGAUSA31.53552-84.19447
6ACKNantucketMAUSA41.25305-70.06018
7ACTWacoTXUSA31.61129-97.23052
8ACVArcata/EurekaCAUSA40.97812-124.10862
9ACYAtlantic CityNJUSA39.45758-74.57717
10ADKAdakAKUSA51.87796-176.64603
11ADQKodiakAKUSA57.74997-152.49386
12AEXAlexandriaLAUSA31.32737-92.54856
13AGSAugustaGAUSA33.36996-81.9645
14AKNKing SalmonAKUSA58.6768-156.64922
15ALBAlbanyNYUSA42.74812-73.80298
16ALOWaterlooIAUSA42.55708-92.40034
17AMAAmarilloTXUSA35.21937-101.70593
18ANCAnchorageAKUSA61.17432-149.99619
19APNAlpenaMIUSA45.07807-83.56029
20ASEAspenCOUSA39.22316-106.86885
21ATLAtlantaGAUSA33.64044-84.42694
22ATWAppletonWIUSA44.25741-88.51948
23AUSAustinTXUSA30.19453-97.66987
24AVLAshevilleNCUSA35.43619-82.54181
25AVPWilkes-Barre/ScrantonPAUSA41.33815-75.72427
26AZOKalamazooMIUSA42.23488-85.55206
27BDLWindsor LocksCTUSA41.93887-72.68323
28BETBethelAKUSA60.77978-161.838
29BFLBakersfieldCAUSA35.4336-119.05677
30BGMBinghamtonNYUSA42.20848-75.97961
31BGRBangorMEUSA44.80744-68.82814
32BHMBirminghamALUSA33.56294-86.75355
33BILBillingsMTUSA45.80766-108.54286
34BISBismarckNDUSA46.77411-100.74672
35BJIBemidjiMNUSA47.50942-94.93372
36BLIBellinghamWAUSA48.79275-122.53753
37BMIBloomingtonILUSA40.47799-88.91595
38BNANashvilleTNUSA36.12448-86.67818
39BOIBoiseIDUSA43.56444-116.22278
40BOSBostonMAUSA42.36435-71.00518
41BPTBeaumont/Port ArthurTXUSA29.95083-94.02069
42BQKBrunswickGAUSA31.25903-81.46631
43BQNAguadillaPRUSA18.49486-67.12944
44BRDBrainerdMNUSA46.39786-94.13723
45BROBrownsvilleTXUSA25.90683-97.42586
46BRWBarrowAKUSA71.28545-156.766
47BTMButteMTUSA45.9548-112.49746
48BTRBaton RougeLAUSA30.53316-91.14963
49BTVBurlingtonVTUSA44.473-73.15031
50BUFBuffaloNYUSA42.94052-78.73217
51BURBurbankCAUSA34.20062-118.3585
52BWIBaltimoreMDUSA39.1754-76.6682
53BZNBozemanMTUSA45.7769-111.15301
54CAEColumbiaSCUSA33.93884-81.11954
55CAKAkronOHUSA40.91631-81.44247
56CDCCedar CityUTUSA37.70097-113.09858
57CDVCordovaAKUSA60.49183-145.47765
58CECCrescent CityCAUSA41.78016-124.23653
59CHAChattanoogaTNUSA35.03527-85.20379
60CHOCharlottesvilleVAUSA38.13864-78.45286
61CHSCharlestonSCUSA32.89865-80.04051
62CIDCedar RapidsIAUSA41.88459-91.71087
63CIUSault Ste. MarieMIUSA46.25075-84.47239
64CLDSan DiegoCAUSA33.12723-117.27873
65CLEClevelandOHUSA41.41089-81.8494
66CLLCollege StationTXUSA30.58859-96.36382
67CLTCharlotteNCUSA35.21401-80.94313
68CMHColumbusOHUSA39.99799-82.89188
69CMIChampaign/UrbanaILUSA40.03925-88.27806
70CMXHancockMIUSA47.16842-88.48906
71CNYMoabUTUSA38.75496-109.75484
72CODCodyWYUSA44.52019-109.0238
73COSColorado SpringsCOUSA38.80581-104.70025
74COUColumbiaMOUSA38.81809-92.21963
75CPRCasperWYUSA42.90836-106.46447
76CRPCorpus ChristiTXUSA27.77036-97.50122
77CRWCharlestonWVUSA38.37315-81.59319
78CSGColumbusGAUSA32.51633-84.93886
79CVGCovingtonKYUSA39.04614-84.66217
80CWAMosineeWIUSA44.77762-89.66678
81DABDaytona BeachFLUSA29.17992-81.05806
82DALDallasTXUSA32.84711-96.85177
83DAYDaytonOHUSA39.90238-84.21938
84DBQDubuqueIAUSA42.40296-90.70917
85DCAArlingtonVAUSA38.85208-77.03772
86DENDenverCOUSA39.85841-104.667
87DFWDallas-Fort WorthTXUSA32.89595-97.0372
88DHNDothanALUSA31.32134-85.44963
89DIKDickinsonNDUSA46.79739-102.80195
90DLGDillinghamAKUSA59.04541-158.50334
91DLHDuluthMNUSA46.84209-92.19365
92DRODurangoCOUSA37.15152-107.75377
93DSMDes MoinesIAUSA41.53493-93.66068
94DTWDetroitMIUSA42.21206-83.34884
95DVLDevils LakeNDUSA48.11425-98.90878
96EAUEau ClaireWIUSA44.86526-91.48507
97ECPPanama CityFLUSA[null][null]
98EGEEagleCOUSA39.64257-106.9177
99EKOElkoNVUSA40.82493-115.7917
100ELMElmiraNYUSA42.15991-76.89144
Columns: 7
" ], "text/plain": [ " IATA_CODE AIRPORT CITY \\\\\n", "1 ABE Lehigh Valley International Airport Allentown \\\\\n", "2 ABI Abilene Regional Airport Abilene \\\\\n", "3 ABQ Albuquerque International Sunport Albuquerque \\\\\n", "4 ABR Aberdeen Regional Airport Aberdeen \\\\\n", "5 ABY Southwest Georgia Regional Airport Albany \\\\\n", "6 ACK Nantucket Memorial Airport Nantucket \\\\\n", "7 ACT Waco Regional Airport Waco \\\\\n", "8 ACV Arcata Airport Arcata/Eureka \\\\\n", "9 ACY Atlantic City International Airport Atlantic City \\\\\n", "10 ADK Adak Airport Adak \\\\\n", "11 ADQ Kodiak Airport Kodiak \\\\\n", "12 AEX Alexandria International Airport Alexandria \\\\\n", "13 AGS Augusta Regional Airport (Bush Field) Augusta \\\\\n", "14 AKN King Salmon Airport King Salmon \\\\\n", "15 ALB Albany International Airport Albany \\\\\n", "16 ALO Waterloo Regional Airport Waterloo \\\\\n", "17 AMA Rick Husband Amarillo International A... Amarillo \\\\\n", "18 ANC Ted Stevens Anchorage International A... Anchorage \\\\\n", "19 APN Alpena County Regional Airport Alpena \\\\\n", "20 ASE Aspen-Pitkin County Airport Aspen \\\\\n", "21 ATL Hartsfield-Jackson Atlanta Internatio... Atlanta \\\\\n", "22 ATW Appleton International Airport Appleton \\\\\n", "23 AUS Austin-Bergstrom International Airport Austin \\\\\n", "24 AVL Asheville Regional Airport Asheville \\\\\n", "25 AVP Wilkes-Barre/Scranton International A... Wilkes-Barre/Scranton \\\\\n", "26 AZO Kalamazoo/Battle Creek International ... Kalamazoo \\\\\n", "27 BDL Bradley International Airport Windsor Locks \\\\\n", "28 BET Bethel Airport Bethel \\\\\n", "29 BFL Meadows Field Bakersfield \\\\\n", "30 BGM Greater Binghamton Airport Binghamton \\\\\n", "31 BGR Bangor International Airport Bangor \\\\\n", "32 BHM Birmingham-Shuttlesworth Internationa... Birmingham \\\\\n", "33 BIL Billings Logan International Airport Billings \\\\\n", "34 BIS Bismarck Municipal Airport Bismarck \\\\\n", "35 BJI Bemidji Regional Airport Bemidji \\\\\n", "36 BLI Bellingham International Airport Bellingham \\\\\n", "37 BMI Central Illinois Regional Airport at ... Bloomington \\\\\n", "38 BNA Nashville International Airport Nashville \\\\\n", "39 BOI Boise Airport (Boise Air Terminal) Boise \\\\\n", "40 BOS Gen. Edward Lawrence Logan Internatio... Boston \\\\\n", "41 BPT Jack Brooks Regional Airport (Southea... Beaumont/Port Arthur \\\\\n", "42 BQK Brunswick Golden Isles Airport Brunswick \\\\\n", "43 BQN Rafael Hernández Airport Aguadilla \\\\\n", "44 BRD Brainerd Lakes Regional Airport Brainerd \\\\\n", "45 BRO Brownsville/South Padre Island Intern... Brownsville \\\\\n", "46 BRW Wiley Post-Will Rogers Memorial Airport Barrow \\\\\n", "47 BTM Bert Mooney Airport Butte \\\\\n", "48 BTR Baton Rouge Metropolitan Airport Baton Rouge \\\\\n", "49 BTV Burlington International Airport Burlington \\\\\n", "50 BUF Buffalo Niagara International Airport Buffalo \\\\\n", "51 BUR Bob Hope Airport (Hollywood Burbank A... Burbank \\\\\n", "52 BWI Baltimore-Washington International Ai... Baltimore \\\\\n", "53 BZN Bozeman Yellowstone International Air... Bozeman \\\\\n", "54 CAE Columbia Metropolitan Airport Columbia \\\\\n", "55 CAK Akron-Canton Regional Airport Akron \\\\\n", "56 CDC Cedar City Regional Airport Cedar City \\\\\n", "57 CDV Merle K. (Mudhole) Smith Airport Cordova \\\\\n", "58 CEC Del Norte County Airport (Jack McNama... Crescent City \\\\\n", "59 CHA Chattanooga Metropolitan Airport (Lov... Chattanooga \\\\\n", "60 CHO Charlottesville-Albemarle Airport Charlottesville \\\\\n", "61 CHS Charleston International Airport/Char... Charleston \\\\\n", "62 CID The Eastern Iowa Airport Cedar Rapids \\\\\n", "63 CIU Chippewa County International Airport Sault Ste. Marie \\\\\n", "64 CLD McClellan-Palomar Airport San Diego \\\\\n", "65 CLE Cleveland Hopkins International Airport Cleveland \\\\\n", "66 CLL Easterwood Airport College Station \\\\\n", "67 CLT Charlotte Douglas International Airport Charlotte \\\\\n", "68 CMH Port Columbus International Airport Columbus \\\\\n", "69 CMI University of Illinois - Willard Airport Champaign/Urbana \\\\\n", "70 CMX Houghton County Memorial Airport Hancock \\\\\n", "71 CNY Canyonlands Field Moab \\\\\n", "72 COD Yellowstone Regional Airport Cody \\\\\n", "73 COS City of Colorado Springs Municipal Ai... Colorado Springs \\\\\n", "74 COU Columbia Regional Airport Columbia \\\\\n", "75 CPR Natrona County International Airport Casper \\\\\n", "76 CRP Corpus Christi International Airport Corpus Christi \\\\\n", "77 CRW Yeager Airport Charleston \\\\\n", "78 CSG Columbus Metropolitan Airport Columbus \\\\\n", "79 CVG Cincinnati/Northern Kentucky Internat... Covington \\\\\n", "80 CWA Central Wisconsin Airport Mosinee \\\\\n", "81 DAB Daytona Beach International Airport Daytona Beach \\\\\n", "82 DAL Dallas Love Field Dallas \\\\\n", "83 DAY James M. Cox Dayton International Air... Dayton \\\\\n", "84 DBQ Dubuque Regional Airport Dubuque \\\\\n", "85 DCA Ronald Reagan Washington National Air... Arlington \\\\\n", "86 DEN Denver International Airport Denver \\\\\n", "87 DFW Dallas/Fort Worth International Airport Dallas-Fort Worth \\\\\n", "88 DHN Dothan Regional Airport Dothan \\\\\n", "89 DIK Dickinson Theodore Roosevelt Regional... Dickinson \\\\\n", "90 DLG Dillingham Airport Dillingham \\\\\n", "91 DLH Duluth International Airport Duluth \\\\\n", "92 DRO Durango-La Plata County Airport Durango \\\\\n", "93 DSM Des Moines International Airport Des Moines \\\\\n", "94 DTW Detroit Metropolitan Airport Detroit \\\\\n", "95 DVL Devils Lake Regional Airport Devils Lake \\\\\n", "96 EAU Chippewa Valley Regional Airport Eau Claire \\\\\n", "97 ECP Northwest Florida Beaches Internation... Panama City \\\\\n", "98 EGE Eagle County Regional Airport Eagle \\\\\n", "99 EKO Elko Regional Airport Elko \\\\\n", "100 ELM Elmira/Corning Regional Airport Elmira \\\\\n", " STATE COUNTRY LATITUDE LONGITUDE \n", "1 PA USA 40.65236 -75.4404 \n", "2 TX USA 32.41132 -99.6819 \n", "3 NM USA 35.04022 -106.60919 \n", "4 SD USA 45.44906 -98.42183 \n", "5 GA USA 31.53552 -84.19447 \n", "6 MA USA 41.25305 -70.06018 \n", "7 TX USA 31.61129 -97.23052 \n", "8 CA USA 40.97812 -124.10862 \n", "9 NJ USA 39.45758 -74.57717 \n", "10 AK USA 51.87796 -176.64603 \n", "11 AK USA 57.74997 -152.49386 \n", "12 LA USA 31.32737 -92.54856 \n", "13 GA USA 33.36996 -81.9645 \n", "14 AK USA 58.6768 -156.64922 \n", "15 NY USA 42.74812 -73.80298 \n", "16 IA USA 42.55708 -92.40034 \n", "17 TX USA 35.21937 -101.70593 \n", "18 AK USA 61.17432 -149.99619 \n", "19 MI USA 45.07807 -83.56029 \n", "20 CO USA 39.22316 -106.86885 \n", "21 GA USA 33.64044 -84.42694 \n", "22 WI USA 44.25741 -88.51948 \n", "23 TX USA 30.19453 -97.66987 \n", "24 NC USA 35.43619 -82.54181 \n", "25 PA USA 41.33815 -75.72427 \n", "26 MI USA 42.23488 -85.55206 \n", "27 CT USA 41.93887 -72.68323 \n", "28 AK USA 60.77978 -161.838 \n", "29 CA USA 35.4336 -119.05677 \n", "30 NY USA 42.20848 -75.97961 \n", "31 ME USA 44.80744 -68.82814 \n", "32 AL USA 33.56294 -86.75355 \n", "33 MT USA 45.80766 -108.54286 \n", "34 ND USA 46.77411 -100.74672 \n", "35 MN USA 47.50942 -94.93372 \n", "36 WA USA 48.79275 -122.53753 \n", "37 IL USA 40.47799 -88.91595 \n", "38 TN USA 36.12448 -86.67818 \n", "39 ID USA 43.56444 -116.22278 \n", "40 MA USA 42.36435 -71.00518 \n", "41 TX USA 29.95083 -94.02069 \n", "42 GA USA 31.25903 -81.46631 \n", "43 PR USA 18.49486 -67.12944 \n", "44 MN USA 46.39786 -94.13723 \n", "45 TX USA 25.90683 -97.42586 \n", "46 AK USA 71.28545 -156.766 \n", "47 MT USA 45.9548 -112.49746 \n", "48 LA USA 30.53316 -91.14963 \n", "49 VT USA 44.473 -73.15031 \n", "50 NY USA 42.94052 -78.73217 \n", "51 CA USA 34.20062 -118.3585 \n", "52 MD USA 39.1754 -76.6682 \n", "53 MT USA 45.7769 -111.15301 \n", "54 SC USA 33.93884 -81.11954 \n", "55 OH USA 40.91631 -81.44247 \n", "56 UT USA 37.70097 -113.09858 \n", "57 AK USA 60.49183 -145.47765 \n", "58 CA USA 41.78016 -124.23653 \n", "59 TN USA 35.03527 -85.20379 \n", "60 VA USA 38.13864 -78.45286 \n", "61 SC USA 32.89865 -80.04051 \n", "62 IA USA 41.88459 -91.71087 \n", "63 MI USA 46.25075 -84.47239 \n", "64 CA USA 33.12723 -117.27873 \n", "65 OH USA 41.41089 -81.8494 \n", "66 TX USA 30.58859 -96.36382 \n", "67 NC USA 35.21401 -80.94313 \n", "68 OH USA 39.99799 -82.89188 \n", "69 IL USA 40.03925 -88.27806 \n", "70 MI USA 47.16842 -88.48906 \n", "71 UT USA 38.75496 -109.75484 \n", "72 WY USA 44.52019 -109.0238 \n", "73 CO USA 38.80581 -104.70025 \n", "74 MO USA 38.81809 -92.21963 \n", "75 WY USA 42.90836 -106.46447 \n", "76 TX USA 27.77036 -97.50122 \n", "77 WV USA 38.37315 -81.59319 \n", "78 GA USA 32.51633 -84.93886 \n", "79 KY USA 39.04614 -84.66217 \n", "80 WI USA 44.77762 -89.66678 \n", "81 FL USA 29.17992 -81.05806 \n", "82 TX USA 32.84711 -96.85177 \n", "83 OH USA 39.90238 -84.21938 \n", "84 IA USA 42.40296 -90.70917 \n", "85 VA USA 38.85208 -77.03772 \n", "86 CO USA 39.85841 -104.667 \n", "87 TX USA 32.89595 -97.0372 \n", "88 AL USA 31.32134 -85.44963 \n", "89 ND USA 46.79739 -102.80195 \n", "90 AK USA 59.04541 -158.50334 \n", "91 MN USA 46.84209 -92.19365 \n", "92 CO USA 37.15152 -107.75377 \n", "93 IA USA 41.53493 -93.66068 \n", "94 MI USA 42.21206 -83.34884 \n", "95 ND USA 48.11425 -98.90878 \n", "96 WI USA 44.86526 -91.48507 \n", "97 FL USA None None \n", "98 CO USA 39.64257 -106.9177 \n", "99 NV USA 40.82493 -115.7917 \n", "100 NY USA 42.15991 -76.89144 \n", "Columns: 7" ] }, "execution_count": 14, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "/* Getting all data from airports table which is placed in the PostgreSQL database represented by \"&\". */\n", "SELECT * FROM &&& airports &&&;" ] }, { "cell_type": "markdown", "id": "58cc0138", "metadata": {}, "source": [ "To perform all regular queries, all we need to do is call the table with its name inside three special characters.\n", "\n", "We'll now try out some queries:\n", "\n", "Count the elements inside the table:" ] }, { "cell_type": "code", "execution_count": 15, "id": "95849c58", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
Execution: 0.107s
" ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "
123
COUNT
Integer
1322
Column: COUNT | Type: Integer
" ], "text/plain": [ " COUNT \n", "1 322 \n", "Column: COUNT | Type: Integer" ] }, "execution_count": 15, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "/* Counting all elements inside the airports table in PostgreSQL. */\n", "SELECT COUNT(*) FROM &&& airports &&&;" ] }, { "cell_type": "markdown", "id": "f44f67a2", "metadata": {}, "source": [ "Find the IATA_CODE where CITY is \"Allentown\":" ] }, { "cell_type": "code", "execution_count": 16, "id": "4f7b42c7", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
Execution: 0.106s
" ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "
Abc
IATA_CODE
Varchar(3)
1ABE
Column: IATA_CODE | Type: Varchar(3)
" ], "text/plain": [ " IATA_CODE \n", "1 ABE \n", "Column: IATA_CODE | Type: Varchar(3)" ] }, "execution_count": 16, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "/* Finding IATA_CODE where the CITY is \"Allentown\" in the airports table. */\n", "SELECT IATA_CODE\n", "FROM &&& airports &&&\n", "WHERE CITY='Allentown';" ] }, { "cell_type": "markdown", "id": "05dc6bf9", "metadata": {}, "source": [ "Note: Any query that we write inside the \"&&&\" signs is also sent to the external database to be run.\n", "\n", "So, instead of just calling the whole table, we can query it using the same special character padding.\n", "\n", "For example, let's select all elements inside the airports table:" ] }, { "cell_type": "code", "execution_count": 17, "id": "ea81e1e9", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
Execution: 0.109s
" ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "
Abc
IATA_CODE
Varchar(3)
Abc
Varchar(100)
Abc
CITY
Varchar(60)
Abc
STATE
Varchar(2)
Abc
COUNTRY
Varchar(100)
🌎
LATITUDE
Numeric(30)
🌎
LONGITUDE
Numeric(30)
1ABEAllentownPAUSA40.65236-75.4404
2ABIAbileneTXUSA32.41132-99.6819
3ABQAlbuquerqueNMUSA35.04022-106.60919
4ABRAberdeenSDUSA45.44906-98.42183
5ABYAlbanyGAUSA31.53552-84.19447
6ACKNantucketMAUSA41.25305-70.06018
7ACTWacoTXUSA31.61129-97.23052
8ACVArcata/EurekaCAUSA40.97812-124.10862
9ACYAtlantic CityNJUSA39.45758-74.57717
10ADKAdakAKUSA51.87796-176.64603
11ADQKodiakAKUSA57.74997-152.49386
12AEXAlexandriaLAUSA31.32737-92.54856
13AGSAugustaGAUSA33.36996-81.9645
14AKNKing SalmonAKUSA58.6768-156.64922
15ALBAlbanyNYUSA42.74812-73.80298
16ALOWaterlooIAUSA42.55708-92.40034
17AMAAmarilloTXUSA35.21937-101.70593
18ANCAnchorageAKUSA61.17432-149.99619
19APNAlpenaMIUSA45.07807-83.56029
20ASEAspenCOUSA39.22316-106.86885
21ATLAtlantaGAUSA33.64044-84.42694
22ATWAppletonWIUSA44.25741-88.51948
23AUSAustinTXUSA30.19453-97.66987
24AVLAshevilleNCUSA35.43619-82.54181
25AVPWilkes-Barre/ScrantonPAUSA41.33815-75.72427
26AZOKalamazooMIUSA42.23488-85.55206
27BDLWindsor LocksCTUSA41.93887-72.68323
28BETBethelAKUSA60.77978-161.838
29BFLBakersfieldCAUSA35.4336-119.05677
30BGMBinghamtonNYUSA42.20848-75.97961
31BGRBangorMEUSA44.80744-68.82814
32BHMBirminghamALUSA33.56294-86.75355
33BILBillingsMTUSA45.80766-108.54286
34BISBismarckNDUSA46.77411-100.74672
35BJIBemidjiMNUSA47.50942-94.93372
36BLIBellinghamWAUSA48.79275-122.53753
37BMIBloomingtonILUSA40.47799-88.91595
38BNANashvilleTNUSA36.12448-86.67818
39BOIBoiseIDUSA43.56444-116.22278
40BOSBostonMAUSA42.36435-71.00518
41BPTBeaumont/Port ArthurTXUSA29.95083-94.02069
42BQKBrunswickGAUSA31.25903-81.46631
43BQNAguadillaPRUSA18.49486-67.12944
44BRDBrainerdMNUSA46.39786-94.13723
45BROBrownsvilleTXUSA25.90683-97.42586
46BRWBarrowAKUSA71.28545-156.766
47BTMButteMTUSA45.9548-112.49746
48BTRBaton RougeLAUSA30.53316-91.14963
49BTVBurlingtonVTUSA44.473-73.15031
50BUFBuffaloNYUSA42.94052-78.73217
51BURBurbankCAUSA34.20062-118.3585
52BWIBaltimoreMDUSA39.1754-76.6682
53BZNBozemanMTUSA45.7769-111.15301
54CAEColumbiaSCUSA33.93884-81.11954
55CAKAkronOHUSA40.91631-81.44247
56CDCCedar CityUTUSA37.70097-113.09858
57CDVCordovaAKUSA60.49183-145.47765
58CECCrescent CityCAUSA41.78016-124.23653
59CHAChattanoogaTNUSA35.03527-85.20379
60CHOCharlottesvilleVAUSA38.13864-78.45286
61CHSCharlestonSCUSA32.89865-80.04051
62CIDCedar RapidsIAUSA41.88459-91.71087
63CIUSault Ste. MarieMIUSA46.25075-84.47239
64CLDSan DiegoCAUSA33.12723-117.27873
65CLEClevelandOHUSA41.41089-81.8494
66CLLCollege StationTXUSA30.58859-96.36382
67CLTCharlotteNCUSA35.21401-80.94313
68CMHColumbusOHUSA39.99799-82.89188
69CMIChampaign/UrbanaILUSA40.03925-88.27806
70CMXHancockMIUSA47.16842-88.48906
71CNYMoabUTUSA38.75496-109.75484
72CODCodyWYUSA44.52019-109.0238
73COSColorado SpringsCOUSA38.80581-104.70025
74COUColumbiaMOUSA38.81809-92.21963
75CPRCasperWYUSA42.90836-106.46447
76CRPCorpus ChristiTXUSA27.77036-97.50122
77CRWCharlestonWVUSA38.37315-81.59319
78CSGColumbusGAUSA32.51633-84.93886
79CVGCovingtonKYUSA39.04614-84.66217
80CWAMosineeWIUSA44.77762-89.66678
81DABDaytona BeachFLUSA29.17992-81.05806
82DALDallasTXUSA32.84711-96.85177
83DAYDaytonOHUSA39.90238-84.21938
84DBQDubuqueIAUSA42.40296-90.70917
85DCAArlingtonVAUSA38.85208-77.03772
86DENDenverCOUSA39.85841-104.667
87DFWDallas-Fort WorthTXUSA32.89595-97.0372
88DHNDothanALUSA31.32134-85.44963
89DIKDickinsonNDUSA46.79739-102.80195
90DLGDillinghamAKUSA59.04541-158.50334
91DLHDuluthMNUSA46.84209-92.19365
92DRODurangoCOUSA37.15152-107.75377
93DSMDes MoinesIAUSA41.53493-93.66068
94DTWDetroitMIUSA42.21206-83.34884
95DVLDevils LakeNDUSA48.11425-98.90878
96EAUEau ClaireWIUSA44.86526-91.48507
97ECPPanama CityFLUSA[null][null]
98EGEEagleCOUSA39.64257-106.9177
99EKOElkoNVUSA40.82493-115.7917
100ELMElmiraNYUSA42.15991-76.89144
Columns: 7
" ], "text/plain": [ " IATA_CODE AIRPORT CITY \\\\\n", "1 ABE Lehigh Valley International Airport Allentown \\\\\n", "2 ABI Abilene Regional Airport Abilene \\\\\n", "3 ABQ Albuquerque International Sunport Albuquerque \\\\\n", "4 ABR Aberdeen Regional Airport Aberdeen \\\\\n", "5 ABY Southwest Georgia Regional Airport Albany \\\\\n", "6 ACK Nantucket Memorial Airport Nantucket \\\\\n", "7 ACT Waco Regional Airport Waco \\\\\n", "8 ACV Arcata Airport Arcata/Eureka \\\\\n", "9 ACY Atlantic City International Airport Atlantic City \\\\\n", "10 ADK Adak Airport Adak \\\\\n", "11 ADQ Kodiak Airport Kodiak \\\\\n", "12 AEX Alexandria International Airport Alexandria \\\\\n", "13 AGS Augusta Regional Airport (Bush Field) Augusta \\\\\n", "14 AKN King Salmon Airport King Salmon \\\\\n", "15 ALB Albany International Airport Albany \\\\\n", "16 ALO Waterloo Regional Airport Waterloo \\\\\n", "17 AMA Rick Husband Amarillo International A... Amarillo \\\\\n", "18 ANC Ted Stevens Anchorage International A... Anchorage \\\\\n", "19 APN Alpena County Regional Airport Alpena \\\\\n", "20 ASE Aspen-Pitkin County Airport Aspen \\\\\n", "21 ATL Hartsfield-Jackson Atlanta Internatio... Atlanta \\\\\n", "22 ATW Appleton International Airport Appleton \\\\\n", "23 AUS Austin-Bergstrom International Airport Austin \\\\\n", "24 AVL Asheville Regional Airport Asheville \\\\\n", "25 AVP Wilkes-Barre/Scranton International A... Wilkes-Barre/Scranton \\\\\n", "26 AZO Kalamazoo/Battle Creek International ... Kalamazoo \\\\\n", "27 BDL Bradley International Airport Windsor Locks \\\\\n", "28 BET Bethel Airport Bethel \\\\\n", "29 BFL Meadows Field Bakersfield \\\\\n", "30 BGM Greater Binghamton Airport Binghamton \\\\\n", "31 BGR Bangor International Airport Bangor \\\\\n", "32 BHM Birmingham-Shuttlesworth Internationa... Birmingham \\\\\n", "33 BIL Billings Logan International Airport Billings \\\\\n", "34 BIS Bismarck Municipal Airport Bismarck \\\\\n", "35 BJI Bemidji Regional Airport Bemidji \\\\\n", "36 BLI Bellingham International Airport Bellingham \\\\\n", "37 BMI Central Illinois Regional Airport at ... Bloomington \\\\\n", "38 BNA Nashville International Airport Nashville \\\\\n", "39 BOI Boise Airport (Boise Air Terminal) Boise \\\\\n", "40 BOS Gen. Edward Lawrence Logan Internatio... Boston \\\\\n", "41 BPT Jack Brooks Regional Airport (Southea... Beaumont/Port Arthur \\\\\n", "42 BQK Brunswick Golden Isles Airport Brunswick \\\\\n", "43 BQN Rafael Hernández Airport Aguadilla \\\\\n", "44 BRD Brainerd Lakes Regional Airport Brainerd \\\\\n", "45 BRO Brownsville/South Padre Island Intern... Brownsville \\\\\n", "46 BRW Wiley Post-Will Rogers Memorial Airport Barrow \\\\\n", "47 BTM Bert Mooney Airport Butte \\\\\n", "48 BTR Baton Rouge Metropolitan Airport Baton Rouge \\\\\n", "49 BTV Burlington International Airport Burlington \\\\\n", "50 BUF Buffalo Niagara International Airport Buffalo \\\\\n", "51 BUR Bob Hope Airport (Hollywood Burbank A... Burbank \\\\\n", "52 BWI Baltimore-Washington International Ai... Baltimore \\\\\n", "53 BZN Bozeman Yellowstone International Air... Bozeman \\\\\n", "54 CAE Columbia Metropolitan Airport Columbia \\\\\n", "55 CAK Akron-Canton Regional Airport Akron \\\\\n", "56 CDC Cedar City Regional Airport Cedar City \\\\\n", "57 CDV Merle K. (Mudhole) Smith Airport Cordova \\\\\n", "58 CEC Del Norte County Airport (Jack McNama... Crescent City \\\\\n", "59 CHA Chattanooga Metropolitan Airport (Lov... Chattanooga \\\\\n", "60 CHO Charlottesville-Albemarle Airport Charlottesville \\\\\n", "61 CHS Charleston International Airport/Char... Charleston \\\\\n", "62 CID The Eastern Iowa Airport Cedar Rapids \\\\\n", "63 CIU Chippewa County International Airport Sault Ste. Marie \\\\\n", "64 CLD McClellan-Palomar Airport San Diego \\\\\n", "65 CLE Cleveland Hopkins International Airport Cleveland \\\\\n", "66 CLL Easterwood Airport College Station \\\\\n", "67 CLT Charlotte Douglas International Airport Charlotte \\\\\n", "68 CMH Port Columbus International Airport Columbus \\\\\n", "69 CMI University of Illinois - Willard Airport Champaign/Urbana \\\\\n", "70 CMX Houghton County Memorial Airport Hancock \\\\\n", "71 CNY Canyonlands Field Moab \\\\\n", "72 COD Yellowstone Regional Airport Cody \\\\\n", "73 COS City of Colorado Springs Municipal Ai... Colorado Springs \\\\\n", "74 COU Columbia Regional Airport Columbia \\\\\n", "75 CPR Natrona County International Airport Casper \\\\\n", "76 CRP Corpus Christi International Airport Corpus Christi \\\\\n", "77 CRW Yeager Airport Charleston \\\\\n", "78 CSG Columbus Metropolitan Airport Columbus \\\\\n", "79 CVG Cincinnati/Northern Kentucky Internat... Covington \\\\\n", "80 CWA Central Wisconsin Airport Mosinee \\\\\n", "81 DAB Daytona Beach International Airport Daytona Beach \\\\\n", "82 DAL Dallas Love Field Dallas \\\\\n", "83 DAY James M. Cox Dayton International Air... Dayton \\\\\n", "84 DBQ Dubuque Regional Airport Dubuque \\\\\n", "85 DCA Ronald Reagan Washington National Air... Arlington \\\\\n", "86 DEN Denver International Airport Denver \\\\\n", "87 DFW Dallas/Fort Worth International Airport Dallas-Fort Worth \\\\\n", "88 DHN Dothan Regional Airport Dothan \\\\\n", "89 DIK Dickinson Theodore Roosevelt Regional... Dickinson \\\\\n", "90 DLG Dillingham Airport Dillingham \\\\\n", "91 DLH Duluth International Airport Duluth \\\\\n", "92 DRO Durango-La Plata County Airport Durango \\\\\n", "93 DSM Des Moines International Airport Des Moines \\\\\n", "94 DTW Detroit Metropolitan Airport Detroit \\\\\n", "95 DVL Devils Lake Regional Airport Devils Lake \\\\\n", "96 EAU Chippewa Valley Regional Airport Eau Claire \\\\\n", "97 ECP Northwest Florida Beaches Internation... Panama City \\\\\n", "98 EGE Eagle County Regional Airport Eagle \\\\\n", "99 EKO Elko Regional Airport Elko \\\\\n", "100 ELM Elmira/Corning Regional Airport Elmira \\\\\n", " STATE COUNTRY LATITUDE LONGITUDE \n", "1 PA USA 40.65236 -75.4404 \n", "2 TX USA 32.41132 -99.6819 \n", "3 NM USA 35.04022 -106.60919 \n", "4 SD USA 45.44906 -98.42183 \n", "5 GA USA 31.53552 -84.19447 \n", "6 MA USA 41.25305 -70.06018 \n", "7 TX USA 31.61129 -97.23052 \n", "8 CA USA 40.97812 -124.10862 \n", "9 NJ USA 39.45758 -74.57717 \n", "10 AK USA 51.87796 -176.64603 \n", "11 AK USA 57.74997 -152.49386 \n", "12 LA USA 31.32737 -92.54856 \n", "13 GA USA 33.36996 -81.9645 \n", "14 AK USA 58.6768 -156.64922 \n", "15 NY USA 42.74812 -73.80298 \n", "16 IA USA 42.55708 -92.40034 \n", "17 TX USA 35.21937 -101.70593 \n", "18 AK USA 61.17432 -149.99619 \n", "19 MI USA 45.07807 -83.56029 \n", "20 CO USA 39.22316 -106.86885 \n", "21 GA USA 33.64044 -84.42694 \n", "22 WI USA 44.25741 -88.51948 \n", "23 TX USA 30.19453 -97.66987 \n", "24 NC USA 35.43619 -82.54181 \n", "25 PA USA 41.33815 -75.72427 \n", "26 MI USA 42.23488 -85.55206 \n", "27 CT USA 41.93887 -72.68323 \n", "28 AK USA 60.77978 -161.838 \n", "29 CA USA 35.4336 -119.05677 \n", "30 NY USA 42.20848 -75.97961 \n", "31 ME USA 44.80744 -68.82814 \n", "32 AL USA 33.56294 -86.75355 \n", "33 MT USA 45.80766 -108.54286 \n", "34 ND USA 46.77411 -100.74672 \n", "35 MN USA 47.50942 -94.93372 \n", "36 WA USA 48.79275 -122.53753 \n", "37 IL USA 40.47799 -88.91595 \n", "38 TN USA 36.12448 -86.67818 \n", "39 ID USA 43.56444 -116.22278 \n", "40 MA USA 42.36435 -71.00518 \n", "41 TX USA 29.95083 -94.02069 \n", "42 GA USA 31.25903 -81.46631 \n", "43 PR USA 18.49486 -67.12944 \n", "44 MN USA 46.39786 -94.13723 \n", "45 TX USA 25.90683 -97.42586 \n", "46 AK USA 71.28545 -156.766 \n", "47 MT USA 45.9548 -112.49746 \n", "48 LA USA 30.53316 -91.14963 \n", "49 VT USA 44.473 -73.15031 \n", "50 NY USA 42.94052 -78.73217 \n", "51 CA USA 34.20062 -118.3585 \n", "52 MD USA 39.1754 -76.6682 \n", "53 MT USA 45.7769 -111.15301 \n", "54 SC USA 33.93884 -81.11954 \n", "55 OH USA 40.91631 -81.44247 \n", "56 UT USA 37.70097 -113.09858 \n", "57 AK USA 60.49183 -145.47765 \n", "58 CA USA 41.78016 -124.23653 \n", "59 TN USA 35.03527 -85.20379 \n", "60 VA USA 38.13864 -78.45286 \n", "61 SC USA 32.89865 -80.04051 \n", "62 IA USA 41.88459 -91.71087 \n", "63 MI USA 46.25075 -84.47239 \n", "64 CA USA 33.12723 -117.27873 \n", "65 OH USA 41.41089 -81.8494 \n", "66 TX USA 30.58859 -96.36382 \n", "67 NC USA 35.21401 -80.94313 \n", "68 OH USA 39.99799 -82.89188 \n", "69 IL USA 40.03925 -88.27806 \n", "70 MI USA 47.16842 -88.48906 \n", "71 UT USA 38.75496 -109.75484 \n", "72 WY USA 44.52019 -109.0238 \n", "73 CO USA 38.80581 -104.70025 \n", "74 MO USA 38.81809 -92.21963 \n", "75 WY USA 42.90836 -106.46447 \n", "76 TX USA 27.77036 -97.50122 \n", "77 WV USA 38.37315 -81.59319 \n", "78 GA USA 32.51633 -84.93886 \n", "79 KY USA 39.04614 -84.66217 \n", "80 WI USA 44.77762 -89.66678 \n", "81 FL USA 29.17992 -81.05806 \n", "82 TX USA 32.84711 -96.85177 \n", "83 OH USA 39.90238 -84.21938 \n", "84 IA USA 42.40296 -90.70917 \n", "85 VA USA 38.85208 -77.03772 \n", "86 CO USA 39.85841 -104.667 \n", "87 TX USA 32.89595 -97.0372 \n", "88 AL USA 31.32134 -85.44963 \n", "89 ND USA 46.79739 -102.80195 \n", "90 AK USA 59.04541 -158.50334 \n", "91 MN USA 46.84209 -92.19365 \n", "92 CO USA 37.15152 -107.75377 \n", "93 IA USA 41.53493 -93.66068 \n", "94 MI USA 42.21206 -83.34884 \n", "95 ND USA 48.11425 -98.90878 \n", "96 WI USA 44.86526 -91.48507 \n", "97 FL USA None None \n", "98 CO USA 39.64257 -106.9177 \n", "99 NV USA 40.82493 -115.7917 \n", "100 NY USA 42.15991 -76.89144 \n", "Columns: 7" ] }, "execution_count": 17, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "/* Getting all data from airports table which is placed in the PostgreSQL database represented by \"$\". */\n", "&&& SELECT * FROM airports &&&;" ] }, { "cell_type": "markdown", "id": "d0901fa6", "metadata": {}, "source": [ "Now we'll run a search query to find a particular id:" ] }, { "cell_type": "code", "execution_count": 18, "id": "3aa34376", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
Execution: 0.113s
" ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "
Abc
IATA_CODE
Varchar(3)
1ABE
Column: IATA_CODE | Type: Varchar(3)
" ], "text/plain": [ " IATA_CODE \n", "1 ABE \n", "Column: IATA_CODE | Type: Varchar(3)" ] }, "execution_count": 18, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "/* Finding IATA_CODE where the CITY is \"Allentown\" in the airports table. */\n", "&&& SELECT \"IATA_CODE\" FROM airports WHERE \"CITY\"='Allentown' &&&;" ] }, { "cell_type": "markdown", "id": "5e0cb551", "metadata": {}, "source": [ "We can also insert a new entry into the airports table, which is placed in the postgreSQL database represented by \"&\":" ] }, { "cell_type": "code", "execution_count": 19, "id": "ab169ac8", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "undefined\n" ] }, { "data": { "text/html": [ "
Execution: 0.284s
" ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "%%sql\n", "/* Inserting an entry into the airportss table which is placed in the postgreSQL database represented by \"&\". */\n", "&&& \n", "INSERT INTO airports \n", " (\"IATA_CODE\", \"AIRPORT\", \"CITY\", \"STATE\", \"COUNTRY\", \"LATITUDE\", \"LONGITUDE\") \n", "VALUES ('MXX' , 'Midway Airport', 'Chicago', 'IL', 'USA', 66.60, 35.00); \n", "&&&" ] }, { "cell_type": "markdown", "id": "0f0d4aff", "metadata": { "tags": [] }, "source": [ "## Connect Multiple Databases" ] }, { "cell_type": "markdown", "id": "e70711f0", "metadata": {}, "source": [ "You can connect and use multiple datasets from different databases. \n", "\n", "In this example we will get:\n", "
    \n", "
  • Airline data from PostgreSQL
    • \n", "
  • Airport data from MySQL
    • \n", "
  • Flights data from Vertica
    • \n", "
\n", "\n", "The datasets can be found here." ] }, { "cell_type": "markdown", "id": "bae6016e", "metadata": { "tags": [] }, "source": [ "### Airline Data in PostgreSQL" ] }, { "cell_type": "markdown", "id": "c0be7cb4", "metadata": {}, "source": [ "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. " ] }, { "cell_type": "code", "execution_count": 3, "id": "c8f542eb", "metadata": {}, "outputs": [], "source": [ "# Setting up a connection with a database given an alias \"pgdb\"\n", "vp.set_external_connection(cid=\"pgdb\",\n", " rowset=500,\n", " symbol=\"$\")" ] }, { "cell_type": "markdown", "id": "8cd73c73", "metadata": {}, "source": [ "Let's look at the airline table that we have in our postgreSQL database." ] }, { "cell_type": "code", "execution_count": 4, "id": "4093caed", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
Execution: 0.125s
" ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "
Abc
IATA_CODE
Char(2)
Abc
AIRLINE
Varchar(1024)
1UAUnited Air Lines Inc.
2AAAmerican Airlines Inc.
3USUS Airways Inc.
4F9Frontier Airlines Inc.
5B6JetBlue Airways
6OOSkywest Airlines Inc.
7ASAlaska Airlines Inc.
8NKSpirit Air Lines
9WNSouthwest Airlines Co.
10DLDelta Air Lines Inc.
11EVAtlantic Southeast Airlines
12HAHawaiian Airlines Inc.
13MQAmerican Eagle Airlines Inc.
14VXVirgin America
15UAUnited Air Lines Inc.
16AAAmerican Airlines Inc.
17USUS Airways Inc.
18F9Frontier Airlines Inc.
19B6JetBlue Airways
20OOSkywest Airlines Inc.
21ASAlaska Airlines Inc.
22NKSpirit Air Lines
23WNSouthwest Airlines Co.
24DLDelta Air Lines Inc.
25EVAtlantic Southeast Airlines
26HAHawaiian Airlines Inc.
27MQAmerican Eagle Airlines Inc.
28VXVirgin America
Columns: 2
" ], "text/plain": [ " IATA_CODE AIRLINE \n", "1 UA United Air Lines Inc. \n", "2 AA American Airlines Inc. \n", "3 US US Airways Inc. \n", "4 F9 Frontier Airlines Inc. \n", "5 B6 JetBlue Airways \n", "6 OO Skywest Airlines Inc. \n", "7 AS Alaska Airlines Inc. \n", "8 NK Spirit Air Lines \n", "9 WN Southwest Airlines Co. \n", "10 DL Delta Air Lines Inc. \n", "11 EV Atlantic Southeast Airlines \n", "12 HA Hawaiian Airlines Inc. \n", "13 MQ American Eagle Airlines Inc. \n", "14 VX Virgin America \n", "15 UA United Air Lines Inc. \n", "16 AA American Airlines Inc. \n", "17 US US Airways Inc. \n", "18 F9 Frontier Airlines Inc. \n", "19 B6 JetBlue Airways \n", "20 OO Skywest Airlines Inc. \n", "21 AS Alaska Airlines Inc. \n", "22 NK Spirit Air Lines \n", "23 WN Southwest Airlines Co. \n", "24 DL Delta Air Lines Inc. \n", "25 EV Atlantic Southeast Airlines \n", "26 HA Hawaiian Airlines Inc. \n", "27 MQ American Eagle Airlines Inc. \n", "28 VX Virgin America \n", "Columns: 2" ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "/* Fetch all the data from the table airports in \"pgdb\" database. */\n", "SELECT * FROM $$$ airline $$$;" ] }, { "cell_type": "markdown", "id": "18cf8f8c", "metadata": { "tags": [] }, "source": [ "### Airports Data in MySQL" ] }, { "cell_type": "markdown", "id": "55ca0032", "metadata": {}, "source": [ "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. " ] }, { "cell_type": "code", "execution_count": 5, "id": "f9fa941d", "metadata": {}, "outputs": [], "source": [ "# Setting up a connection with a database given an alias \"mysql\"\n", "vp.set_external_connection(cid=\"mysql\",\n", " rowset=500,\n", " symbol=\"&\")" ] }, { "cell_type": "markdown", "id": "b2056990", "metadata": {}, "source": [ "Let's take a look at the airports table that we have in our MySQL database." ] }, { "cell_type": "code", "execution_count": 6, "id": "a0cf302b", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
Execution: 0.106s
" ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "
Abc
IATA_CODE
Varchar(3)
Abc
Varchar(100)
Abc
CITY
Varchar(60)
Abc
STATE
Varchar(2)
Abc
COUNTRY
Varchar(100)
🌎
LATITUDE
Numeric(30)
🌎
LONGITUDE
Numeric(30)
1ABEAllentownPAUSA40.65236-75.4404
2ABIAbileneTXUSA32.41132-99.6819
3ABQAlbuquerqueNMUSA35.04022-106.60919
4ABRAberdeenSDUSA45.44906-98.42183
5ABYAlbanyGAUSA31.53552-84.19447
6ACKNantucketMAUSA41.25305-70.06018
7ACTWacoTXUSA31.61129-97.23052
8ACVArcata/EurekaCAUSA40.97812-124.10862
9ACYAtlantic CityNJUSA39.45758-74.57717
10ADKAdakAKUSA51.87796-176.64603
11ADQKodiakAKUSA57.74997-152.49386
12AEXAlexandriaLAUSA31.32737-92.54856
13AGSAugustaGAUSA33.36996-81.9645
14AKNKing SalmonAKUSA58.6768-156.64922
15ALBAlbanyNYUSA42.74812-73.80298
16ALOWaterlooIAUSA42.55708-92.40034
17AMAAmarilloTXUSA35.21937-101.70593
18ANCAnchorageAKUSA61.17432-149.99619
19APNAlpenaMIUSA45.07807-83.56029
20ASEAspenCOUSA39.22316-106.86885
21ATLAtlantaGAUSA33.64044-84.42694
22ATWAppletonWIUSA44.25741-88.51948
23AUSAustinTXUSA30.19453-97.66987
24AVLAshevilleNCUSA35.43619-82.54181
25AVPWilkes-Barre/ScrantonPAUSA41.33815-75.72427
26AZOKalamazooMIUSA42.23488-85.55206
27BDLWindsor LocksCTUSA41.93887-72.68323
28BETBethelAKUSA60.77978-161.838
29BFLBakersfieldCAUSA35.4336-119.05677
30BGMBinghamtonNYUSA42.20848-75.97961
31BGRBangorMEUSA44.80744-68.82814
32BHMBirminghamALUSA33.56294-86.75355
33BILBillingsMTUSA45.80766-108.54286
34BISBismarckNDUSA46.77411-100.74672
35BJIBemidjiMNUSA47.50942-94.93372
36BLIBellinghamWAUSA48.79275-122.53753
37BMIBloomingtonILUSA40.47799-88.91595
38BNANashvilleTNUSA36.12448-86.67818
39BOIBoiseIDUSA43.56444-116.22278
40BOSBostonMAUSA42.36435-71.00518
41BPTBeaumont/Port ArthurTXUSA29.95083-94.02069
42BQKBrunswickGAUSA31.25903-81.46631
43BQNAguadillaPRUSA18.49486-67.12944
44BRDBrainerdMNUSA46.39786-94.13723
45BROBrownsvilleTXUSA25.90683-97.42586
46BRWBarrowAKUSA71.28545-156.766
47BTMButteMTUSA45.9548-112.49746
48BTRBaton RougeLAUSA30.53316-91.14963
49BTVBurlingtonVTUSA44.473-73.15031
50BUFBuffaloNYUSA42.94052-78.73217
51BURBurbankCAUSA34.20062-118.3585
52BWIBaltimoreMDUSA39.1754-76.6682
53BZNBozemanMTUSA45.7769-111.15301
54CAEColumbiaSCUSA33.93884-81.11954
55CAKAkronOHUSA40.91631-81.44247
56CDCCedar CityUTUSA37.70097-113.09858
57CDVCordovaAKUSA60.49183-145.47765
58CECCrescent CityCAUSA41.78016-124.23653
59CHAChattanoogaTNUSA35.03527-85.20379
60CHOCharlottesvilleVAUSA38.13864-78.45286
61CHSCharlestonSCUSA32.89865-80.04051
62CIDCedar RapidsIAUSA41.88459-91.71087
63CIUSault Ste. MarieMIUSA46.25075-84.47239
64CLDSan DiegoCAUSA33.12723-117.27873
65CLEClevelandOHUSA41.41089-81.8494
66CLLCollege StationTXUSA30.58859-96.36382
67CLTCharlotteNCUSA35.21401-80.94313
68CMHColumbusOHUSA39.99799-82.89188
69CMIChampaign/UrbanaILUSA40.03925-88.27806
70CMXHancockMIUSA47.16842-88.48906
71CNYMoabUTUSA38.75496-109.75484
72CODCodyWYUSA44.52019-109.0238
73COSColorado SpringsCOUSA38.80581-104.70025
74COUColumbiaMOUSA38.81809-92.21963
75CPRCasperWYUSA42.90836-106.46447
76CRPCorpus ChristiTXUSA27.77036-97.50122
77CRWCharlestonWVUSA38.37315-81.59319
78CSGColumbusGAUSA32.51633-84.93886
79CVGCovingtonKYUSA39.04614-84.66217
80CWAMosineeWIUSA44.77762-89.66678
81DABDaytona BeachFLUSA29.17992-81.05806
82DALDallasTXUSA32.84711-96.85177
83DAYDaytonOHUSA39.90238-84.21938
84DBQDubuqueIAUSA42.40296-90.70917
85DCAArlingtonVAUSA38.85208-77.03772
86DENDenverCOUSA39.85841-104.667
87DFWDallas-Fort WorthTXUSA32.89595-97.0372
88DHNDothanALUSA31.32134-85.44963
89DIKDickinsonNDUSA46.79739-102.80195
90DLGDillinghamAKUSA59.04541-158.50334
91DLHDuluthMNUSA46.84209-92.19365
92DRODurangoCOUSA37.15152-107.75377
93DSMDes MoinesIAUSA41.53493-93.66068
94DTWDetroitMIUSA42.21206-83.34884
95DVLDevils LakeNDUSA48.11425-98.90878
96EAUEau ClaireWIUSA44.86526-91.48507
97ECPPanama CityFLUSA[null][null]
98EGEEagleCOUSA39.64257-106.9177
99EKOElkoNVUSA40.82493-115.7917
100ELMElmiraNYUSA42.15991-76.89144
Columns: 7
" ], "text/plain": [ " IATA_CODE AIRPORT CITY \\\\\n", "1 ABE Lehigh Valley International Airport Allentown \\\\\n", "2 ABI Abilene Regional Airport Abilene \\\\\n", "3 ABQ Albuquerque International Sunport Albuquerque \\\\\n", "4 ABR Aberdeen Regional Airport Aberdeen \\\\\n", "5 ABY Southwest Georgia Regional Airport Albany \\\\\n", "6 ACK Nantucket Memorial Airport Nantucket \\\\\n", "7 ACT Waco Regional Airport Waco \\\\\n", "8 ACV Arcata Airport Arcata/Eureka \\\\\n", "9 ACY Atlantic City International Airport Atlantic City \\\\\n", "10 ADK Adak Airport Adak \\\\\n", "11 ADQ Kodiak Airport Kodiak \\\\\n", "12 AEX Alexandria International Airport Alexandria \\\\\n", "13 AGS Augusta Regional Airport (Bush Field) Augusta \\\\\n", "14 AKN King Salmon Airport King Salmon \\\\\n", "15 ALB Albany International Airport Albany \\\\\n", "16 ALO Waterloo Regional Airport Waterloo \\\\\n", "17 AMA Rick Husband Amarillo International A... Amarillo \\\\\n", "18 ANC Ted Stevens Anchorage International A... Anchorage \\\\\n", "19 APN Alpena County Regional Airport Alpena \\\\\n", "20 ASE Aspen-Pitkin County Airport Aspen \\\\\n", "21 ATL Hartsfield-Jackson Atlanta Internatio... Atlanta \\\\\n", "22 ATW Appleton International Airport Appleton \\\\\n", "23 AUS Austin-Bergstrom International Airport Austin \\\\\n", "24 AVL Asheville Regional Airport Asheville \\\\\n", "25 AVP Wilkes-Barre/Scranton International A... Wilkes-Barre/Scranton \\\\\n", "26 AZO Kalamazoo/Battle Creek International ... Kalamazoo \\\\\n", "27 BDL Bradley International Airport Windsor Locks \\\\\n", "28 BET Bethel Airport Bethel \\\\\n", "29 BFL Meadows Field Bakersfield \\\\\n", "30 BGM Greater Binghamton Airport Binghamton \\\\\n", "31 BGR Bangor International Airport Bangor \\\\\n", "32 BHM Birmingham-Shuttlesworth Internationa... Birmingham \\\\\n", "33 BIL Billings Logan International Airport Billings \\\\\n", "34 BIS Bismarck Municipal Airport Bismarck \\\\\n", "35 BJI Bemidji Regional Airport Bemidji \\\\\n", "36 BLI Bellingham International Airport Bellingham \\\\\n", "37 BMI Central Illinois Regional Airport at ... Bloomington \\\\\n", "38 BNA Nashville International Airport Nashville \\\\\n", "39 BOI Boise Airport (Boise Air Terminal) Boise \\\\\n", "40 BOS Gen. Edward Lawrence Logan Internatio... Boston \\\\\n", "41 BPT Jack Brooks Regional Airport (Southea... Beaumont/Port Arthur \\\\\n", "42 BQK Brunswick Golden Isles Airport Brunswick \\\\\n", "43 BQN Rafael Hernández Airport Aguadilla \\\\\n", "44 BRD Brainerd Lakes Regional Airport Brainerd \\\\\n", "45 BRO Brownsville/South Padre Island Intern... Brownsville \\\\\n", "46 BRW Wiley Post-Will Rogers Memorial Airport Barrow \\\\\n", "47 BTM Bert Mooney Airport Butte \\\\\n", "48 BTR Baton Rouge Metropolitan Airport Baton Rouge \\\\\n", "49 BTV Burlington International Airport Burlington \\\\\n", "50 BUF Buffalo Niagara International Airport Buffalo \\\\\n", "51 BUR Bob Hope Airport (Hollywood Burbank A... Burbank \\\\\n", "52 BWI Baltimore-Washington International Ai... Baltimore \\\\\n", "53 BZN Bozeman Yellowstone International Air... Bozeman \\\\\n", "54 CAE Columbia Metropolitan Airport Columbia \\\\\n", "55 CAK Akron-Canton Regional Airport Akron \\\\\n", "56 CDC Cedar City Regional Airport Cedar City \\\\\n", "57 CDV Merle K. (Mudhole) Smith Airport Cordova \\\\\n", "58 CEC Del Norte County Airport (Jack McNama... Crescent City \\\\\n", "59 CHA Chattanooga Metropolitan Airport (Lov... Chattanooga \\\\\n", "60 CHO Charlottesville-Albemarle Airport Charlottesville \\\\\n", "61 CHS Charleston International Airport/Char... Charleston \\\\\n", "62 CID The Eastern Iowa Airport Cedar Rapids \\\\\n", "63 CIU Chippewa County International Airport Sault Ste. Marie \\\\\n", "64 CLD McClellan-Palomar Airport San Diego \\\\\n", "65 CLE Cleveland Hopkins International Airport Cleveland \\\\\n", "66 CLL Easterwood Airport College Station \\\\\n", "67 CLT Charlotte Douglas International Airport Charlotte \\\\\n", "68 CMH Port Columbus International Airport Columbus \\\\\n", "69 CMI University of Illinois - Willard Airport Champaign/Urbana \\\\\n", "70 CMX Houghton County Memorial Airport Hancock \\\\\n", "71 CNY Canyonlands Field Moab \\\\\n", "72 COD Yellowstone Regional Airport Cody \\\\\n", "73 COS City of Colorado Springs Municipal Ai... Colorado Springs \\\\\n", "74 COU Columbia Regional Airport Columbia \\\\\n", "75 CPR Natrona County International Airport Casper \\\\\n", "76 CRP Corpus Christi International Airport Corpus Christi \\\\\n", "77 CRW Yeager Airport Charleston \\\\\n", "78 CSG Columbus Metropolitan Airport Columbus \\\\\n", "79 CVG Cincinnati/Northern Kentucky Internat... Covington \\\\\n", "80 CWA Central Wisconsin Airport Mosinee \\\\\n", "81 DAB Daytona Beach International Airport Daytona Beach \\\\\n", "82 DAL Dallas Love Field Dallas \\\\\n", "83 DAY James M. Cox Dayton International Air... Dayton \\\\\n", "84 DBQ Dubuque Regional Airport Dubuque \\\\\n", "85 DCA Ronald Reagan Washington National Air... Arlington \\\\\n", "86 DEN Denver International Airport Denver \\\\\n", "87 DFW Dallas/Fort Worth International Airport Dallas-Fort Worth \\\\\n", "88 DHN Dothan Regional Airport Dothan \\\\\n", "89 DIK Dickinson Theodore Roosevelt Regional... Dickinson \\\\\n", "90 DLG Dillingham Airport Dillingham \\\\\n", "91 DLH Duluth International Airport Duluth \\\\\n", "92 DRO Durango-La Plata County Airport Durango \\\\\n", "93 DSM Des Moines International Airport Des Moines \\\\\n", "94 DTW Detroit Metropolitan Airport Detroit \\\\\n", "95 DVL Devils Lake Regional Airport Devils Lake \\\\\n", "96 EAU Chippewa Valley Regional Airport Eau Claire \\\\\n", "97 ECP Northwest Florida Beaches Internation... Panama City \\\\\n", "98 EGE Eagle County Regional Airport Eagle \\\\\n", "99 EKO Elko Regional Airport Elko \\\\\n", "100 ELM Elmira/Corning Regional Airport Elmira \\\\\n", " STATE COUNTRY LATITUDE LONGITUDE \n", "1 PA USA 40.65236 -75.4404 \n", "2 TX USA 32.41132 -99.6819 \n", "3 NM USA 35.04022 -106.60919 \n", "4 SD USA 45.44906 -98.42183 \n", "5 GA USA 31.53552 -84.19447 \n", "6 MA USA 41.25305 -70.06018 \n", "7 TX USA 31.61129 -97.23052 \n", "8 CA USA 40.97812 -124.10862 \n", "9 NJ USA 39.45758 -74.57717 \n", "10 AK USA 51.87796 -176.64603 \n", "11 AK USA 57.74997 -152.49386 \n", "12 LA USA 31.32737 -92.54856 \n", "13 GA USA 33.36996 -81.9645 \n", "14 AK USA 58.6768 -156.64922 \n", "15 NY USA 42.74812 -73.80298 \n", "16 IA USA 42.55708 -92.40034 \n", "17 TX USA 35.21937 -101.70593 \n", "18 AK USA 61.17432 -149.99619 \n", "19 MI USA 45.07807 -83.56029 \n", "20 CO USA 39.22316 -106.86885 \n", "21 GA USA 33.64044 -84.42694 \n", "22 WI USA 44.25741 -88.51948 \n", "23 TX USA 30.19453 -97.66987 \n", "24 NC USA 35.43619 -82.54181 \n", "25 PA USA 41.33815 -75.72427 \n", "26 MI USA 42.23488 -85.55206 \n", "27 CT USA 41.93887 -72.68323 \n", "28 AK USA 60.77978 -161.838 \n", "29 CA USA 35.4336 -119.05677 \n", "30 NY USA 42.20848 -75.97961 \n", "31 ME USA 44.80744 -68.82814 \n", "32 AL USA 33.56294 -86.75355 \n", "33 MT USA 45.80766 -108.54286 \n", "34 ND USA 46.77411 -100.74672 \n", "35 MN USA 47.50942 -94.93372 \n", "36 WA USA 48.79275 -122.53753 \n", "37 IL USA 40.47799 -88.91595 \n", "38 TN USA 36.12448 -86.67818 \n", "39 ID USA 43.56444 -116.22278 \n", "40 MA USA 42.36435 -71.00518 \n", "41 TX USA 29.95083 -94.02069 \n", "42 GA USA 31.25903 -81.46631 \n", "43 PR USA 18.49486 -67.12944 \n", "44 MN USA 46.39786 -94.13723 \n", "45 TX USA 25.90683 -97.42586 \n", "46 AK USA 71.28545 -156.766 \n", "47 MT USA 45.9548 -112.49746 \n", "48 LA USA 30.53316 -91.14963 \n", "49 VT USA 44.473 -73.15031 \n", "50 NY USA 42.94052 -78.73217 \n", "51 CA USA 34.20062 -118.3585 \n", "52 MD USA 39.1754 -76.6682 \n", "53 MT USA 45.7769 -111.15301 \n", "54 SC USA 33.93884 -81.11954 \n", "55 OH USA 40.91631 -81.44247 \n", "56 UT USA 37.70097 -113.09858 \n", "57 AK USA 60.49183 -145.47765 \n", "58 CA USA 41.78016 -124.23653 \n", "59 TN USA 35.03527 -85.20379 \n", "60 VA USA 38.13864 -78.45286 \n", "61 SC USA 32.89865 -80.04051 \n", "62 IA USA 41.88459 -91.71087 \n", "63 MI USA 46.25075 -84.47239 \n", "64 CA USA 33.12723 -117.27873 \n", "65 OH USA 41.41089 -81.8494 \n", "66 TX USA 30.58859 -96.36382 \n", "67 NC USA 35.21401 -80.94313 \n", "68 OH USA 39.99799 -82.89188 \n", "69 IL USA 40.03925 -88.27806 \n", "70 MI USA 47.16842 -88.48906 \n", "71 UT USA 38.75496 -109.75484 \n", "72 WY USA 44.52019 -109.0238 \n", "73 CO USA 38.80581 -104.70025 \n", "74 MO USA 38.81809 -92.21963 \n", "75 WY USA 42.90836 -106.46447 \n", "76 TX USA 27.77036 -97.50122 \n", "77 WV USA 38.37315 -81.59319 \n", "78 GA USA 32.51633 -84.93886 \n", "79 KY USA 39.04614 -84.66217 \n", "80 WI USA 44.77762 -89.66678 \n", "81 FL USA 29.17992 -81.05806 \n", "82 TX USA 32.84711 -96.85177 \n", "83 OH USA 39.90238 -84.21938 \n", "84 IA USA 42.40296 -90.70917 \n", "85 VA USA 38.85208 -77.03772 \n", "86 CO USA 39.85841 -104.667 \n", "87 TX USA 32.89595 -97.0372 \n", "88 AL USA 31.32134 -85.44963 \n", "89 ND USA 46.79739 -102.80195 \n", "90 AK USA 59.04541 -158.50334 \n", "91 MN USA 46.84209 -92.19365 \n", "92 CO USA 37.15152 -107.75377 \n", "93 IA USA 41.53493 -93.66068 \n", "94 MI USA 42.21206 -83.34884 \n", "95 ND USA 48.11425 -98.90878 \n", "96 WI USA 44.86526 -91.48507 \n", "97 FL USA None None \n", "98 CO USA 39.64257 -106.9177 \n", "99 NV USA 40.82493 -115.7917 \n", "100 NY USA 42.15991 -76.89144 \n", "Columns: 7" ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "/* Fetch all the data from the table airports in \"mysql\" database */\n", "SELECT * FROM &&& airports &&&;" ] }, { "cell_type": "markdown", "id": "ad91d2ad", "metadata": { "tags": [] }, "source": [ "### Flights Data Vertica" ] }, { "cell_type": "markdown", "id": "3867a9dc", "metadata": {}, "source": [ "We'll now read a locally stored CSV file with the flights data and materialize it in Vertica. " ] }, { "cell_type": "code", "execution_count": 7, "id": "1f04eb00", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
123
YEAR
Int
123
MONTH
Int
123
DAY
Int
123
DAY_OF_WEEK
Int
Abc
AIRLINE
Varchar(20)
123
FLIGHT_NUMBER
Int
Abc
TAIL_NUMBER
Varchar(20)
Abc
ORIGIN_AIRPORT
Varchar(20)
Abc
DESTINATION_AIRPORT
Varchar(20)
123
SCHEDULED_DEPARTURE
Int
123
DEPARTURE_TIME
Int
123
DEPARTURE_DELAY
Int
123
TAXI_OUT
Int
123
WHEELS_OFF
Int
123
SCHEDULED_TIME
Int
123
ELAPSED_TIME
Int
123
AIR_TIME
Int
123
DISTANCE
Int
123
WHEELS_ON
Int
123
TAXI_IN
Int
123
SCHEDULED_ARRIVAL
Int
123
ARRIVAL_TIME
Int
123
ARRIVAL_DELAY
Int
123
DIVERTED
Int
123
CANCELLED
Int
Abc
CANCELLATION_REASON
Varchar(20)
123
AIR_SYSTEM_DELAY
Int
123
SECURITY_DELAY
Int
123
AIRLINE_DELAY
Int
123
LATE_AIRCRAFT_DELAY
Int
123
WEATHER_DELAY
Int
12015114AA1N787AAJFKLAX900855-51791239040237824751230712301237700[null][null][null][null][null][null]
22015114AA2N795AALAXJFK900856-41691233529527124751643817351651-4400[null][null][null][null][null][null]
32015114AA3N798AAJFKLAX12301226-419124538038235824751543515501548-200[null][null][null][null][null][null]
42015114AA4N799AALAXJFK12201214-6231237330319284247520211220502033-1700[null][null][null][null][null][null]
52015114AA5N376AADFWHNL130517542892118155155264993784223461740224030000[null]110197920
62015114AA6N398AAOGGDFW1805[null][null][null][null]425[null][null]3711[null][null]510[null][null]01A[null][null][null][null][null]
72015114AA7N398AADFWOGG121515131782415375005174903711194731635195019500[null]17017800
82015114AA8N368AAHNLDFW1745193310815194844544642037846481151065910900[null]1001080
92015114AA9N792AAJFKLAX700649-112271138039736824751019710201026600[null][null][null][null][null][null]
102015114AA10N796AALAXJFK21502150014220430929427524755395559544-1500[null][null][null][null][null][null]
112015114AA12N792AALAXJFK11151114-1191133335321287247519201519501935-1500[null][null][null][null][null][null]
122015114AA14N5ERAAOGGLAX23002254-6122306305289267248653310605543-2200[null][null][null][null][null][null]
132015114AA15N783AAJFKSFO800755-557852400453388258612208114012284800[null]480000
142015114AA16N783AASFOJFK12451316311813343303333052586213910211521493400[null]300310
152015114AA17N019AAATLMIA700656-415711112106825948339852842-1000[null][null][null][null][null][null]
162015114AA19N786AAJFKLAX11001055-517111238037835424751406714201413-700[null][null][null][null][null][null]
172015114AA20N789AASFOJFK15001503319152232532830125862323823252331600[null][null][null][null][null][null]
182015114AA21N795AAJFKLAX1845185162919203854073682475222810221022382800[null]220600
192015114AA22N786AALAXJFK15151512-3231535320317277247523121723352329-600[null][null][null][null][null][null]
202015114AA23N371AADFWSFO755841463591623023319014641026894510344900[null]304600
212015114AA23N371AASFODFW10501123332811512102171781464164911162017004000[null]700330
222015114AA24N784AASFOJFK720714-62073434033230725861541516001546-1400[null][null][null][null][null][null]
232015114AA25N3AVAABOSLAX900857-31591239540538126111233912351242700[null][null][null][null][null][null]
242015114AA27N5DUAADFWLAS1850192939161945175165141105520068194520142900[null]002900
252015114AA28N358AALAXMIA15001456-4191515290305284234222592225023011100[null][null][null][null][null][null]
262015114AA29N455AAONTDFW122513569125142117521714911881850431720193313300[null]4209100
272015114AA29N568AADFWONT1035[null][null][null][null]190[null][null]1188[null][null]1145[null][null]01B[null][null][null][null][null]
282015114AA30N795AALAXJFK23302326-423234932029927024757196750725-2500[null][null][null][null][null][null]
292015114AA31N5ESAALAXHNL8058282312840365362346255612264121012302000[null]002000
302015114AA32N794AALAXJFK141514249141438325315288247522261322402239-100[null][null][null][null][null][null]
312015114AA35N3ALAADFWHDN11101109-1111120145178111769121156123513073200[null]320000
322015114AA35N3ALAAHDNDFW1320140040111411135130109769170010163517103500[null]007280
332015114AA36N3EKAASANDFW935928-713941170172140117114011914251420-500[null][null][null][null][null][null]
342015114AA37N483AADFWSAN21002103318212118518716611712207322052210500[null][null][null][null][null][null]
352015114AA40N3BYAADCAORD9559550910041301261116121055611051101-400[null][null][null][null][null][null]
362015114AA41N3KEAAORDSEA20552054-11621102702932361721230641232523472200[null]220000
372015114AA42N3GPAASEAORD850849-11690523522420117211426714451433-1200[null][null][null][null][null][null]
382015114AA43N3BNAADFWDTW1030[null][null][null][null]150[null][null]986[null][null]1400[null][null]01B[null][null][null][null][null]
392015114AA43N3KCAADTWDFW144514450191504180199163986164717164517041900[null]190000
402015114AA44N3AJAALASJFK810803-71782029527224822481528716051535-3000[null][null][null][null][null][null]
412015114AA45N3ELAAJFKLAS17291727-221174835534731822482006820242014-1000[null][null][null][null][null][null]
422015114AA46N3CSAASJCORD725725013738255253226182913241413401338-200[null][null][null][null][null][null]
432015114AA47N3KSAAORDSJC1915203580182053280261238182922515215522566100[null]000610
442015114AA48N3JXAASANDFW835830-513843175164141117113041013301314-1600[null][null][null][null][null][null]
452015114AA49N5ELAADFWSAN1935200631152021185178160117121013204021042400[null]002400
462015114AA50N570AASLCDFW81582493690016517913498912149120012232300[null]140900
472015114AA51N471AADFWSLC17252035190242059170161130989220971915221618100[null]00221590
482015114AA52N3LMAALAXDCA12251222-318124029027925323111953820152001-1400[null][null][null][null][null][null]
492015114AA53N3LHAADCALAX840838-21285036035033123111121711401128-1200[null][null][null][null][null][null]
502015114AA54N3CWAALASORD910906-41692221519317015141412714451419-2600[null][null][null][null][null][null]
512015114AA55N3JJAAORDLAS1820192464131937230225205151421027201021095900[null]005900
522015114AA58N5DFAAKOALAX1400140002214223103202762504205822211021201000[null][null][null][null][null][null]
532015114AA59N5DFAALAXKOA840837-31785435035233225041226312301229-100[null][null][null][null][null][null]
542015114AA60N372AADFWMIA175419551211520101601511331121232332134232611200[null]0011200
552015114AA61N850AAMIADFW715719413732199192167112191912934931-300[null][null][null][null][null][null]
562015114AA63N3ECAAMIAPHX19501955523201831730227119722249823072257-1000[null][null][null][null][null][null]
572015114AA64N3HXAADFWJFK102510411613105420018716913911443514451448300[null][null][null][null][null][null]
582015114AA65N3HLAAJFKDFW16151625101916442552942271391193148193020194900[null]3901000
592015114AA66N631AASJUJFK11511142-922120424524722115981445414561449-700[null][null][null][null][null][null]
602015114AA67N631AAJFKSJU160516292423165223521118215982054621002100000[null][null][null][null][null][null]
612015114AA68N5EGAASFOMIA70070331471732431729525851512815241520-400[null][null][null][null][null][null]
622015114AA69N3HTAAMIASFO18351836112184838635934225852130522012135-2600[null][null][null][null][null][null]
632015114AA70N5BTAASANDFW705659-622721180171137117111381212051150-1500[null][null][null][null][null][null]
642015114AA71N3GHAADFWSAN18051803-214181718017616011711857219051859-600[null][null][null][null][null][null]
652015114AA72N5EKAADFWMCO6006066186241451421209859244925928300[null][null][null][null][null][null]
662015114AA72N5EKAAMCODFW10201015-516103117517114398511541212151206-900[null][null][null][null][null][null]
672015114AA73N3FBAAPHLCLT1005957-8261023120108764491139612051145-2000[null][null][null][null][null][null]
682015114AA74N488AACLEDFW15551552-311160319018116110211744918051753-1200[null][null][null][null][null][null]
692015114AA74N505AADFWCLE1145[null][null][null][null]150[null][null]1021[null][null]1515[null][null]01B[null][null][null][null][null]
702015114AA75N3HPAAIADLAX18301822-817183936035833522882114621302120-1000[null][null][null][null][null][null]
712015114AA76N3HPAALAXIAD950946-418100428526524322881707417351711-2400[null][null][null][null][null][null]
722015114AA76N3HPAASFOLAX730723-71573885974933782733855900500[null][null][null][null][null][null]
732015114AA79N520AADFWSMF2140221939302249225224188143123576232533800[null]000380
742015114AA80N4XTAATUSDFW1655174146101751130130105813203615200520514600[null]000460
752015114AA81N4XCAADFWTUS22452308231323211401401238132445282300[null]000230
762015114AA83N4XBAAMCODFW735733-21674918017414698591512935927-800[null][null][null][null][null][null]
772015114AA84N3AXAABOSJFK940930-10159457569461871031810551039-1600[null][null][null][null][null][null]
782015114AA84N3AXAAJFKBOS7307344177517065441878354840839-100[null][null][null][null][null][null]
792015114AA85N785AAJFKSFO15301524-620154440038636125861845519101850-2000[null][null][null][null][null][null]
802015114AA86N3GTAAPDXORD705701-41571623523020817391244713001251-900[null][null][null][null][null][null]
812015114AA87N490AAORDRNO18451840-511185126522821316712024421102028-4200[null][null][null][null][null][null]
822015114AA89N3KVAAIAHMIA520618581963714113711196492878419355400[null]005400
832015114AA92N3KHAASANORD90090001591524021018917231424615001430-3000[null][null][null][null][null][null]
842015114AA93N3HHAAORDSAN1720180545101815255222210172319452193519471200[null][null][null][null][null][null]
852015114AA94N3ANAASANJFK755810151382332028126224461545616151551-2400[null][null][null][null][null][null]
862015114AA95N3ANAAJFKSAN1720174929181807380386363244621105204021153500[null]602900
872015114AA96N553AADFWSTL82083111218529594705501002395510051000[null][null][null][null][null][null]
882015114AA96N553AASTLDFW103510511615110611513198550124418123013023200[null]1606100
892015114AA97N3ENAADCADFW845842-314856215210185119211011111201112-800[null][null][null][null][null][null]
902015114AA99N3KMAAORDPDX15501553311160427525323917391803318251806-1900[null][null][null][null][null][null]
912015114AA102N376AAHNLDFW1920235627617134454454173784111011645112127600[null]0002760
922015114AA103N4XSAADFWMSY141014332313144680806244715485153015532300[null]002300
932015114AA103N4XSAAMSYDFW16101624148163210091744471746917501755500[null][null][null][null][null][null]
942015114AA110N3LRAASNAORD805801-47808230211194172613221013551332-2300[null][null][null][null][null][null]
952015114AA111N3LYAAORDSNA17201719-111173026023722217261912419401916-2400[null][null][null][null][null][null]
962015114AA112N857AADENMIA101510172191036224229199170915551115591606700[null][null][null][null][null][null]
972015114AA113N3GRAAMIADEN211521150212136269269235170923311323442344000[null][null][null][null][null][null]
982015114AA115N3CTAALAXMIA105103-21411728627625523428327851839-1200[null][null][null][null][null][null]
992015114AA116N383AAOGGDFW1955201621102026430433418371172457057292400[null]302100
1002015114AA117N793AAJFKLAX14451438-728150639038935524751801618151807-800[null][null][null][null][null][null]
Rows: 1-100 | Columns: 31
" ], "text/plain": [ " YEAR MONTH DAY DAY_OF_WEEK AIRLINE \\\\\n", "1 2015 1 1 4 AA \\\\\n", "2 2015 1 1 4 AA \\\\\n", "3 2015 1 1 4 AA \\\\\n", "4 2015 1 1 4 AA \\\\\n", "5 2015 1 1 4 AA \\\\\n", "6 2015 1 1 4 AA \\\\\n", "7 2015 1 1 4 AA \\\\\n", "8 2015 1 1 4 AA \\\\\n", "9 2015 1 1 4 AA \\\\\n", "10 2015 1 1 4 AA \\\\\n", "11 2015 1 1 4 AA \\\\\n", "12 2015 1 1 4 AA \\\\\n", "13 2015 1 1 4 AA \\\\\n", "14 2015 1 1 4 AA \\\\\n", "15 2015 1 1 4 AA \\\\\n", "16 2015 1 1 4 AA \\\\\n", "17 2015 1 1 4 AA \\\\\n", "18 2015 1 1 4 AA \\\\\n", "19 2015 1 1 4 AA \\\\\n", "20 2015 1 1 4 AA \\\\\n", "21 2015 1 1 4 AA \\\\\n", "22 2015 1 1 4 AA \\\\\n", "23 2015 1 1 4 AA \\\\\n", "24 2015 1 1 4 AA \\\\\n", "25 2015 1 1 4 AA \\\\\n", "26 2015 1 1 4 AA \\\\\n", "27 2015 1 1 4 AA \\\\\n", "28 2015 1 1 4 AA \\\\\n", "29 2015 1 1 4 AA \\\\\n", "30 2015 1 1 4 AA \\\\\n", "31 2015 1 1 4 AA \\\\\n", "32 2015 1 1 4 AA \\\\\n", "33 2015 1 1 4 AA \\\\\n", "34 2015 1 1 4 AA \\\\\n", "35 2015 1 1 4 AA \\\\\n", "36 2015 1 1 4 AA \\\\\n", "37 2015 1 1 4 AA \\\\\n", "38 2015 1 1 4 AA \\\\\n", "39 2015 1 1 4 AA \\\\\n", "40 2015 1 1 4 AA \\\\\n", "41 2015 1 1 4 AA \\\\\n", "42 2015 1 1 4 AA \\\\\n", "43 2015 1 1 4 AA \\\\\n", "44 2015 1 1 4 AA \\\\\n", "45 2015 1 1 4 AA \\\\\n", "46 2015 1 1 4 AA \\\\\n", "47 2015 1 1 4 AA \\\\\n", "48 2015 1 1 4 AA \\\\\n", "49 2015 1 1 4 AA \\\\\n", "50 2015 1 1 4 AA \\\\\n", "51 2015 1 1 4 AA \\\\\n", "52 2015 1 1 4 AA \\\\\n", "53 2015 1 1 4 AA \\\\\n", "54 2015 1 1 4 AA \\\\\n", "55 2015 1 1 4 AA \\\\\n", "56 2015 1 1 4 AA \\\\\n", "57 2015 1 1 4 AA \\\\\n", "58 2015 1 1 4 AA \\\\\n", "59 2015 1 1 4 AA \\\\\n", "60 2015 1 1 4 AA \\\\\n", "61 2015 1 1 4 AA \\\\\n", "62 2015 1 1 4 AA \\\\\n", "63 2015 1 1 4 AA \\\\\n", "64 2015 1 1 4 AA \\\\\n", "65 2015 1 1 4 AA \\\\\n", "66 2015 1 1 4 AA \\\\\n", "67 2015 1 1 4 AA \\\\\n", "68 2015 1 1 4 AA \\\\\n", "69 2015 1 1 4 AA \\\\\n", "70 2015 1 1 4 AA \\\\\n", "71 2015 1 1 4 AA \\\\\n", "72 2015 1 1 4 AA \\\\\n", "73 2015 1 1 4 AA \\\\\n", "74 2015 1 1 4 AA \\\\\n", "75 2015 1 1 4 AA \\\\\n", "76 2015 1 1 4 AA \\\\\n", "77 2015 1 1 4 AA \\\\\n", "78 2015 1 1 4 AA \\\\\n", "79 2015 1 1 4 AA \\\\\n", "80 2015 1 1 4 AA \\\\\n", "81 2015 1 1 4 AA \\\\\n", "82 2015 1 1 4 AA \\\\\n", "83 2015 1 1 4 AA \\\\\n", "84 2015 1 1 4 AA \\\\\n", "85 2015 1 1 4 AA \\\\\n", "86 2015 1 1 4 AA \\\\\n", "87 2015 1 1 4 AA \\\\\n", "88 2015 1 1 4 AA \\\\\n", "89 2015 1 1 4 AA \\\\\n", "90 2015 1 1 4 AA \\\\\n", "91 2015 1 1 4 AA \\\\\n", "92 2015 1 1 4 AA \\\\\n", "93 2015 1 1 4 AA \\\\\n", "94 2015 1 1 4 AA \\\\\n", "95 2015 1 1 4 AA \\\\\n", "96 2015 1 1 4 AA \\\\\n", "97 2015 1 1 4 AA \\\\\n", "98 2015 1 1 4 AA \\\\\n", "99 2015 1 1 4 AA \\\\\n", "100 2015 1 1 4 AA \\\\\n", " FLIGHT_NUMBER TAIL_NUMBER ORIGIN_AIRPORT DESTINATION_AIRPORT \\\\\n", "1 1 N787AA JFK LAX \\\\\n", "2 2 N795AA LAX JFK \\\\\n", "3 3 N798AA JFK LAX \\\\\n", "4 4 N799AA LAX JFK \\\\\n", "5 5 N376AA DFW HNL \\\\\n", "6 6 N398AA OGG DFW \\\\\n", "7 7 N398AA DFW OGG \\\\\n", "8 8 N368AA HNL DFW \\\\\n", "9 9 N792AA JFK LAX \\\\\n", "10 10 N796AA LAX JFK \\\\\n", "11 12 N792AA LAX JFK \\\\\n", "12 14 N5ERAA OGG LAX \\\\\n", "13 15 N783AA JFK SFO \\\\\n", "14 16 N783AA SFO JFK \\\\\n", "15 17 N019AA ATL MIA \\\\\n", "16 19 N786AA JFK LAX \\\\\n", "17 20 N789AA SFO JFK \\\\\n", "18 21 N795AA JFK LAX \\\\\n", "19 22 N786AA LAX JFK \\\\\n", "20 23 N371AA DFW SFO \\\\\n", "21 23 N371AA SFO DFW \\\\\n", "22 24 N784AA SFO JFK \\\\\n", "23 25 N3AVAA BOS LAX \\\\\n", "24 27 N5DUAA DFW LAS \\\\\n", "25 28 N358AA LAX MIA \\\\\n", "26 29 N455AA ONT DFW \\\\\n", "27 29 N568AA DFW ONT \\\\\n", "28 30 N795AA LAX JFK \\\\\n", "29 31 N5ESAA LAX HNL \\\\\n", "30 32 N794AA LAX JFK \\\\\n", "31 35 N3ALAA DFW HDN \\\\\n", "32 35 N3ALAA HDN DFW \\\\\n", "33 36 N3EKAA SAN DFW \\\\\n", "34 37 N483AA DFW SAN \\\\\n", "35 40 N3BYAA DCA ORD \\\\\n", "36 41 N3KEAA ORD SEA \\\\\n", "37 42 N3GPAA SEA ORD \\\\\n", "38 43 N3BNAA DFW DTW \\\\\n", "39 43 N3KCAA DTW DFW \\\\\n", "40 44 N3AJAA LAS JFK \\\\\n", "41 45 N3ELAA JFK LAS \\\\\n", "42 46 N3CSAA SJC ORD \\\\\n", "43 47 N3KSAA ORD SJC \\\\\n", "44 48 N3JXAA SAN DFW \\\\\n", "45 49 N5ELAA DFW SAN \\\\\n", "46 50 N570AA SLC DFW \\\\\n", "47 51 N471AA DFW SLC \\\\\n", "48 52 N3LMAA LAX DCA \\\\\n", "49 53 N3LHAA DCA LAX \\\\\n", "50 54 N3CWAA LAS ORD \\\\\n", "51 55 N3JJAA ORD LAS \\\\\n", "52 58 N5DFAA KOA LAX \\\\\n", "53 59 N5DFAA LAX KOA \\\\\n", "54 60 N372AA DFW MIA \\\\\n", "55 61 N850AA MIA DFW \\\\\n", "56 63 N3ECAA MIA PHX \\\\\n", "57 64 N3HXAA DFW JFK \\\\\n", "58 65 N3HLAA JFK DFW \\\\\n", "59 66 N631AA SJU JFK \\\\\n", "60 67 N631AA JFK SJU \\\\\n", "61 68 N5EGAA SFO MIA \\\\\n", "62 69 N3HTAA MIA SFO \\\\\n", "63 70 N5BTAA SAN DFW \\\\\n", "64 71 N3GHAA DFW SAN \\\\\n", "65 72 N5EKAA DFW MCO \\\\\n", "66 72 N5EKAA MCO DFW \\\\\n", "67 73 N3FBAA PHL CLT \\\\\n", "68 74 N488AA CLE DFW \\\\\n", "69 74 N505AA DFW CLE \\\\\n", "70 75 N3HPAA IAD LAX \\\\\n", "71 76 N3HPAA LAX IAD \\\\\n", "72 76 N3HPAA SFO LAX \\\\\n", "73 79 N520AA DFW SMF \\\\\n", "74 80 N4XTAA TUS DFW \\\\\n", "75 81 N4XCAA DFW TUS \\\\\n", "76 83 N4XBAA MCO DFW \\\\\n", "77 84 N3AXAA BOS JFK \\\\\n", "78 84 N3AXAA JFK BOS \\\\\n", "79 85 N785AA JFK SFO \\\\\n", "80 86 N3GTAA PDX ORD \\\\\n", "81 87 N490AA ORD RNO \\\\\n", "82 89 N3KVAA IAH MIA \\\\\n", "83 92 N3KHAA SAN ORD \\\\\n", "84 93 N3HHAA ORD SAN \\\\\n", "85 94 N3ANAA SAN JFK \\\\\n", "86 95 N3ANAA JFK SAN \\\\\n", "87 96 N553AA DFW STL \\\\\n", "88 96 N553AA STL DFW \\\\\n", "89 97 N3ENAA DCA DFW \\\\\n", "90 99 N3KMAA ORD PDX \\\\\n", "91 102 N376AA HNL DFW \\\\\n", "92 103 N4XSAA DFW MSY \\\\\n", "93 103 N4XSAA MSY DFW \\\\\n", "94 110 N3LRAA SNA ORD \\\\\n", "95 111 N3LYAA ORD SNA \\\\\n", "96 112 N857AA DEN MIA \\\\\n", "97 113 N3GRAA MIA DEN \\\\\n", "98 115 N3CTAA LAX MIA \\\\\n", "99 116 N383AA OGG DFW \\\\\n", "100 117 N793AA JFK LAX \\\\\n", " SCHEDULED_DEPARTURE DEPARTURE_TIME DEPARTURE_DELAY \\\\\n", "1 900 855 -5 \\\\\n", "2 900 856 -4 \\\\\n", "3 1230 1226 -4 \\\\\n", "4 1220 1214 -6 \\\\\n", "5 1305 1754 289 \\\\\n", "6 1805 None None \\\\\n", "7 1215 1513 178 \\\\\n", "8 1745 1933 108 \\\\\n", "9 700 649 -11 \\\\\n", "10 2150 2150 0 \\\\\n", "11 1115 1114 -1 \\\\\n", "12 2300 2254 -6 \\\\\n", "13 800 755 -5 \\\\\n", "14 1245 1316 31 \\\\\n", "15 700 656 -4 \\\\\n", "16 1100 1055 -5 \\\\\n", "17 1500 1503 3 \\\\\n", "18 1845 1851 6 \\\\\n", "19 1515 1512 -3 \\\\\n", "20 755 841 46 \\\\\n", "21 1050 1123 33 \\\\\n", "22 720 714 -6 \\\\\n", "23 900 857 -3 \\\\\n", "24 1850 1929 39 \\\\\n", "25 1500 1456 -4 \\\\\n", "26 1225 1356 91 \\\\\n", "27 1035 None None \\\\\n", "28 2330 2326 -4 \\\\\n", "29 805 828 23 \\\\\n", "30 1415 1424 9 \\\\\n", "31 1110 1109 -1 \\\\\n", "32 1320 1400 40 \\\\\n", "33 935 928 -7 \\\\\n", "34 2100 2103 3 \\\\\n", "35 955 955 0 \\\\\n", "36 2055 2054 -1 \\\\\n", "37 850 849 -1 \\\\\n", "38 1030 None None \\\\\n", "39 1445 1445 0 \\\\\n", "40 810 803 -7 \\\\\n", "41 1729 1727 -2 \\\\\n", "42 725 725 0 \\\\\n", "43 1915 2035 80 \\\\\n", "44 835 830 -5 \\\\\n", "45 1935 2006 31 \\\\\n", "46 815 824 9 \\\\\n", "47 1725 2035 190 \\\\\n", "48 1225 1222 -3 \\\\\n", "49 840 838 -2 \\\\\n", "50 910 906 -4 \\\\\n", "51 1820 1924 64 \\\\\n", "52 1400 1400 0 \\\\\n", "53 840 837 -3 \\\\\n", "54 1754 1955 121 \\\\\n", "55 715 719 4 \\\\\n", "56 1950 1955 5 \\\\\n", "57 1025 1041 16 \\\\\n", "58 1615 1625 10 \\\\\n", "59 1151 1142 -9 \\\\\n", "60 1605 1629 24 \\\\\n", "61 700 703 3 \\\\\n", "62 1835 1836 1 \\\\\n", "63 705 659 -6 \\\\\n", "64 1805 1803 -2 \\\\\n", "65 600 606 6 \\\\\n", "66 1020 1015 -5 \\\\\n", "67 1005 957 -8 \\\\\n", "68 1555 1552 -3 \\\\\n", "69 1145 None None \\\\\n", "70 1830 1822 -8 \\\\\n", "71 950 946 -4 \\\\\n", "72 730 723 -7 \\\\\n", "73 2140 2219 39 \\\\\n", "74 1655 1741 46 \\\\\n", "75 2245 2308 23 \\\\\n", "76 735 733 -2 \\\\\n", "77 940 930 -10 \\\\\n", "78 730 734 4 \\\\\n", "79 1530 1524 -6 \\\\\n", "80 705 701 -4 \\\\\n", "81 1845 1840 -5 \\\\\n", "82 520 618 58 \\\\\n", "83 900 900 0 \\\\\n", "84 1720 1805 45 \\\\\n", "85 755 810 15 \\\\\n", "86 1720 1749 29 \\\\\n", "87 820 831 11 \\\\\n", "88 1035 1051 16 \\\\\n", "89 845 842 -3 \\\\\n", "90 1550 1553 3 \\\\\n", "91 1920 2356 276 \\\\\n", "92 1410 1433 23 \\\\\n", "93 1610 1624 14 \\\\\n", "94 805 801 -4 \\\\\n", "95 1720 1719 -1 \\\\\n", "96 1015 1017 2 \\\\\n", "97 2115 2115 0 \\\\\n", "98 105 103 -2 \\\\\n", "99 1955 2016 21 \\\\\n", "100 1445 1438 -7 \\\\\n", " TAXI_OUT WHEELS_OFF SCHEDULED_TIME ELAPSED_TIME \\\\\n", "1 17 912 390 402 \\\\\n", "2 16 912 335 295 \\\\\n", "3 19 1245 380 382 \\\\\n", "4 23 1237 330 319 \\\\\n", "5 21 1815 515 526 \\\\\n", "6 None None 425 None \\\\\n", "7 24 1537 500 517 \\\\\n", "8 15 1948 445 446 \\\\\n", "9 22 711 380 397 \\\\\n", "10 14 2204 309 294 \\\\\n", "11 19 1133 335 321 \\\\\n", "12 12 2306 305 289 \\\\\n", "13 57 852 400 453 \\\\\n", "14 18 1334 330 333 \\\\\n", "15 15 711 112 106 \\\\\n", "16 17 1112 380 378 \\\\\n", "17 19 1522 325 328 \\\\\n", "18 29 1920 385 407 \\\\\n", "19 23 1535 320 317 \\\\\n", "20 35 916 230 233 \\\\\n", "21 28 1151 210 217 \\\\\n", "22 20 734 340 332 \\\\\n", "23 15 912 395 405 \\\\\n", "24 16 1945 175 165 \\\\\n", "25 19 1515 290 305 \\\\\n", "26 25 1421 175 217 \\\\\n", "27 None None 190 None \\\\\n", "28 23 2349 320 299 \\\\\n", "29 12 840 365 362 \\\\\n", "30 14 1438 325 315 \\\\\n", "31 11 1120 145 178 \\\\\n", "32 11 1411 135 130 \\\\\n", "33 13 941 170 172 \\\\\n", "34 18 2121 185 187 \\\\\n", "35 9 1004 130 126 \\\\\n", "36 16 2110 270 293 \\\\\n", "37 16 905 235 224 \\\\\n", "38 None None 150 None \\\\\n", "39 19 1504 180 199 \\\\\n", "40 17 820 295 272 \\\\\n", "41 21 1748 355 347 \\\\\n", "42 13 738 255 253 \\\\\n", "43 18 2053 280 261 \\\\\n", "44 13 843 175 164 \\\\\n", "45 15 2021 185 178 \\\\\n", "46 36 900 165 179 \\\\\n", "47 24 2059 170 161 \\\\\n", "48 18 1240 290 279 \\\\\n", "49 12 850 360 350 \\\\\n", "50 16 922 215 193 \\\\\n", "51 13 1937 230 225 \\\\\n", "52 22 1422 310 320 \\\\\n", "53 17 854 350 352 \\\\\n", "54 15 2010 160 151 \\\\\n", "55 13 732 199 192 \\\\\n", "56 23 2018 317 302 \\\\\n", "57 13 1054 200 187 \\\\\n", "58 19 1644 255 294 \\\\\n", "59 22 1204 245 247 \\\\\n", "60 23 1652 235 211 \\\\\n", "61 14 717 324 317 \\\\\n", "62 12 1848 386 359 \\\\\n", "63 22 721 180 171 \\\\\n", "64 14 1817 180 176 \\\\\n", "65 18 624 145 142 \\\\\n", "66 16 1031 175 171 \\\\\n", "67 26 1023 120 108 \\\\\n", "68 11 1603 190 181 \\\\\n", "69 None None 150 None \\\\\n", "70 17 1839 360 358 \\\\\n", "71 18 1004 285 265 \\\\\n", "72 15 738 85 97 \\\\\n", "73 30 2249 225 224 \\\\\n", "74 10 1751 130 130 \\\\\n", "75 13 2321 140 140 \\\\\n", "76 16 749 180 174 \\\\\n", "77 15 945 75 69 \\\\\n", "78 17 751 70 65 \\\\\n", "79 20 1544 400 386 \\\\\n", "80 15 716 235 230 \\\\\n", "81 11 1851 265 228 \\\\\n", "82 19 637 141 137 \\\\\n", "83 15 915 240 210 \\\\\n", "84 10 1815 255 222 \\\\\n", "85 13 823 320 281 \\\\\n", "86 18 1807 380 386 \\\\\n", "87 21 852 95 94 \\\\\n", "88 15 1106 115 131 \\\\\n", "89 14 856 215 210 \\\\\n", "90 11 1604 275 253 \\\\\n", "91 17 13 445 445 \\\\\n", "92 13 1446 80 80 \\\\\n", "93 8 1632 100 91 \\\\\n", "94 7 808 230 211 \\\\\n", "95 11 1730 260 237 \\\\\n", "96 19 1036 224 229 \\\\\n", "97 21 2136 269 269 \\\\\n", "98 14 117 286 276 \\\\\n", "99 10 2026 430 433 \\\\\n", "100 28 1506 390 389 \\\\\n", " AIR_TIME DISTANCE WHEELS_ON TAXI_IN SCHEDULED_ARRIVAL \\\\\n", "1 378 2475 1230 7 1230 \\\\\n", "2 271 2475 1643 8 1735 \\\\\n", "3 358 2475 1543 5 1550 \\\\\n", "4 284 2475 2021 12 2050 \\\\\n", "5 499 3784 2234 6 1740 \\\\\n", "6 None 3711 None None 510 \\\\\n", "7 490 3711 1947 3 1635 \\\\\n", "8 420 3784 648 11 510 \\\\\n", "9 368 2475 1019 7 1020 \\\\\n", "10 275 2475 539 5 559 \\\\\n", "11 287 2475 1920 15 1950 \\\\\n", "12 267 2486 533 10 605 \\\\\n", "13 388 2586 1220 8 1140 \\\\\n", "14 305 2586 2139 10 2115 \\\\\n", "15 82 594 833 9 852 \\\\\n", "16 354 2475 1406 7 1420 \\\\\n", "17 301 2586 2323 8 2325 \\\\\n", "18 368 2475 2228 10 2210 \\\\\n", "19 277 2475 2312 17 2335 \\\\\n", "20 190 1464 1026 8 945 \\\\\n", "21 178 1464 1649 11 1620 \\\\\n", "22 307 2586 1541 5 1600 \\\\\n", "23 381 2611 1233 9 1235 \\\\\n", "24 141 1055 2006 8 1945 \\\\\n", "25 284 2342 2259 2 2250 \\\\\n", "26 149 1188 1850 43 1720 \\\\\n", "27 None 1188 None None 1145 \\\\\n", "28 270 2475 719 6 750 \\\\\n", "29 346 2556 1226 4 1210 \\\\\n", "30 288 2475 2226 13 2240 \\\\\n", "31 111 769 1211 56 1235 \\\\\n", "32 109 769 1700 10 1635 \\\\\n", "33 140 1171 1401 19 1425 \\\\\n", "34 166 1171 2207 3 2205 \\\\\n", "35 111 612 1055 6 1105 \\\\\n", "36 236 1721 2306 41 2325 \\\\\n", "37 201 1721 1426 7 1445 \\\\\n", "38 None 986 None None 1400 \\\\\n", "39 163 986 1647 17 1645 \\\\\n", "40 248 2248 1528 7 1605 \\\\\n", "41 318 2248 2006 8 2024 \\\\\n", "42 226 1829 1324 14 1340 \\\\\n", "43 238 1829 2251 5 2155 \\\\\n", "44 141 1171 1304 10 1330 \\\\\n", "45 160 1171 2101 3 2040 \\\\\n", "46 134 989 1214 9 1200 \\\\\n", "47 130 989 2209 7 1915 \\\\\n", "48 253 2311 1953 8 2015 \\\\\n", "49 331 2311 1121 7 1140 \\\\\n", "50 170 1514 1412 7 1445 \\\\\n", "51 205 1514 2102 7 2010 \\\\\n", "52 276 2504 2058 22 2110 \\\\\n", "53 332 2504 1226 3 1230 \\\\\n", "54 133 1121 2323 3 2134 \\\\\n", "55 167 1121 919 12 934 \\\\\n", "56 271 1972 2249 8 2307 \\\\\n", "57 169 1391 1443 5 1445 \\\\\n", "58 227 1391 1931 48 1930 \\\\\n", "59 221 1598 1445 4 1456 \\\\\n", "60 182 1598 2054 6 2100 \\\\\n", "61 295 2585 1512 8 1524 \\\\\n", "62 342 2585 2130 5 2201 \\\\\n", "63 137 1171 1138 12 1205 \\\\\n", "64 160 1171 1857 2 1905 \\\\\n", "65 120 985 924 4 925 \\\\\n", "66 143 985 1154 12 1215 \\\\\n", "67 76 449 1139 6 1205 \\\\\n", "68 161 1021 1744 9 1805 \\\\\n", "69 None 1021 None None 1515 \\\\\n", "70 335 2288 2114 6 2130 \\\\\n", "71 243 2288 1707 4 1735 \\\\\n", "72 49 337 827 33 855 \\\\\n", "73 188 1431 2357 6 2325 \\\\\n", "74 105 813 2036 15 2005 \\\\\n", "75 123 813 24 4 5 \\\\\n", "76 146 985 915 12 935 \\\\\n", "77 46 187 1031 8 1055 \\\\\n", "78 44 187 835 4 840 \\\\\n", "79 361 2586 1845 5 1910 \\\\\n", "80 208 1739 1244 7 1300 \\\\\n", "81 213 1671 2024 4 2110 \\\\\n", "82 111 964 928 7 841 \\\\\n", "83 189 1723 1424 6 1500 \\\\\n", "84 210 1723 1945 2 1935 \\\\\n", "85 262 2446 1545 6 1615 \\\\\n", "86 363 2446 2110 5 2040 \\\\\n", "87 70 550 1002 3 955 \\\\\n", "88 98 550 1244 18 1230 \\\\\n", "89 185 1192 1101 11 1120 \\\\\n", "90 239 1739 1803 3 1825 \\\\\n", "91 417 3784 1110 11 645 \\\\\n", "92 62 447 1548 5 1530 \\\\\n", "93 74 447 1746 9 1750 \\\\\n", "94 194 1726 1322 10 1355 \\\\\n", "95 222 1726 1912 4 1940 \\\\\n", "96 199 1709 1555 11 1559 \\\\\n", "97 235 1709 2331 13 2344 \\\\\n", "98 255 2342 832 7 851 \\\\\n", "99 418 3711 724 5 705 \\\\\n", "100 355 2475 1801 6 1815 \\\\\n", " ARRIVAL_TIME ARRIVAL_DELAY DIVERTED CANCELLED \\\\\n", "1 1237 7 0 0 \\\\\n", "2 1651 -44 0 0 \\\\\n", "3 1548 -2 0 0 \\\\\n", "4 2033 -17 0 0 \\\\\n", "5 2240 300 0 0 \\\\\n", "6 None None 0 1 \\\\\n", "7 1950 195 0 0 \\\\\n", "8 659 109 0 0 \\\\\n", "9 1026 6 0 0 \\\\\n", "10 544 -15 0 0 \\\\\n", "11 1935 -15 0 0 \\\\\n", "12 543 -22 0 0 \\\\\n", "13 1228 48 0 0 \\\\\n", "14 2149 34 0 0 \\\\\n", "15 842 -10 0 0 \\\\\n", "16 1413 -7 0 0 \\\\\n", "17 2331 6 0 0 \\\\\n", "18 2238 28 0 0 \\\\\n", "19 2329 -6 0 0 \\\\\n", "20 1034 49 0 0 \\\\\n", "21 1700 40 0 0 \\\\\n", "22 1546 -14 0 0 \\\\\n", "23 1242 7 0 0 \\\\\n", "24 2014 29 0 0 \\\\\n", "25 2301 11 0 0 \\\\\n", "26 1933 133 0 0 \\\\\n", "27 None None 0 1 \\\\\n", "28 725 -25 0 0 \\\\\n", "29 1230 20 0 0 \\\\\n", "30 2239 -1 0 0 \\\\\n", "31 1307 32 0 0 \\\\\n", "32 1710 35 0 0 \\\\\n", "33 1420 -5 0 0 \\\\\n", "34 2210 5 0 0 \\\\\n", "35 1101 -4 0 0 \\\\\n", "36 2347 22 0 0 \\\\\n", "37 1433 -12 0 0 \\\\\n", "38 None None 0 1 \\\\\n", "39 1704 19 0 0 \\\\\n", "40 1535 -30 0 0 \\\\\n", "41 2014 -10 0 0 \\\\\n", "42 1338 -2 0 0 \\\\\n", "43 2256 61 0 0 \\\\\n", "44 1314 -16 0 0 \\\\\n", "45 2104 24 0 0 \\\\\n", "46 1223 23 0 0 \\\\\n", "47 2216 181 0 0 \\\\\n", "48 2001 -14 0 0 \\\\\n", "49 1128 -12 0 0 \\\\\n", "50 1419 -26 0 0 \\\\\n", "51 2109 59 0 0 \\\\\n", "52 2120 10 0 0 \\\\\n", "53 1229 -1 0 0 \\\\\n", "54 2326 112 0 0 \\\\\n", "55 931 -3 0 0 \\\\\n", "56 2257 -10 0 0 \\\\\n", "57 1448 3 0 0 \\\\\n", "58 2019 49 0 0 \\\\\n", "59 1449 -7 0 0 \\\\\n", "60 2100 0 0 0 \\\\\n", "61 1520 -4 0 0 \\\\\n", "62 2135 -26 0 0 \\\\\n", "63 1150 -15 0 0 \\\\\n", "64 1859 -6 0 0 \\\\\n", "65 928 3 0 0 \\\\\n", "66 1206 -9 0 0 \\\\\n", "67 1145 -20 0 0 \\\\\n", "68 1753 -12 0 0 \\\\\n", "69 None None 0 1 \\\\\n", "70 2120 -10 0 0 \\\\\n", "71 1711 -24 0 0 \\\\\n", "72 900 5 0 0 \\\\\n", "73 3 38 0 0 \\\\\n", "74 2051 46 0 0 \\\\\n", "75 28 23 0 0 \\\\\n", "76 927 -8 0 0 \\\\\n", "77 1039 -16 0 0 \\\\\n", "78 839 -1 0 0 \\\\\n", "79 1850 -20 0 0 \\\\\n", "80 1251 -9 0 0 \\\\\n", "81 2028 -42 0 0 \\\\\n", "82 935 54 0 0 \\\\\n", "83 1430 -30 0 0 \\\\\n", "84 1947 12 0 0 \\\\\n", "85 1551 -24 0 0 \\\\\n", "86 2115 35 0 0 \\\\\n", "87 1005 10 0 0 \\\\\n", "88 1302 32 0 0 \\\\\n", "89 1112 -8 0 0 \\\\\n", "90 1806 -19 0 0 \\\\\n", "91 1121 276 0 0 \\\\\n", "92 1553 23 0 0 \\\\\n", "93 1755 5 0 0 \\\\\n", "94 1332 -23 0 0 \\\\\n", "95 1916 -24 0 0 \\\\\n", "96 1606 7 0 0 \\\\\n", "97 2344 0 0 0 \\\\\n", "98 839 -12 0 0 \\\\\n", "99 729 24 0 0 \\\\\n", "100 1807 -8 0 0 \\\\\n", " CANCELLATION_REASON AIR_SYSTEM_DELAY SECURITY_DELAY \\\\\n", "1 None None None \\\\\n", "2 None None None \\\\\n", "3 None None None \\\\\n", "4 None None None \\\\\n", "5 None 11 0 \\\\\n", "6 A None None \\\\\n", "7 None 17 0 \\\\\n", "8 None 1 0 \\\\\n", "9 None None None \\\\\n", "10 None None None \\\\\n", "11 None None None \\\\\n", "12 None None None \\\\\n", "13 None 48 0 \\\\\n", "14 None 3 0 \\\\\n", "15 None None None \\\\\n", "16 None None None \\\\\n", "17 None None None \\\\\n", "18 None 22 0 \\\\\n", "19 None None None \\\\\n", "20 None 3 0 \\\\\n", "21 None 7 0 \\\\\n", "22 None None None \\\\\n", "23 None None None \\\\\n", "24 None 0 0 \\\\\n", "25 None None None \\\\\n", "26 None 42 0 \\\\\n", "27 B None None \\\\\n", "28 None None None \\\\\n", "29 None 0 0 \\\\\n", "30 None None None \\\\\n", "31 None 32 0 \\\\\n", "32 None 0 0 \\\\\n", "33 None None None \\\\\n", "34 None None None \\\\\n", "35 None None None \\\\\n", "36 None 22 0 \\\\\n", "37 None None None \\\\\n", "38 B None None \\\\\n", "39 None 19 0 \\\\\n", "40 None None None \\\\\n", "41 None None None \\\\\n", "42 None None None \\\\\n", "43 None 0 0 \\\\\n", "44 None None None \\\\\n", "45 None 0 0 \\\\\n", "46 None 14 0 \\\\\n", "47 None 0 0 \\\\\n", "48 None None None \\\\\n", "49 None None None \\\\\n", "50 None None None \\\\\n", "51 None 0 0 \\\\\n", "52 None None None \\\\\n", "53 None None None \\\\\n", "54 None 0 0 \\\\\n", "55 None None None \\\\\n", "56 None None None \\\\\n", "57 None None None \\\\\n", "58 None 39 0 \\\\\n", "59 None None None \\\\\n", "60 None None None \\\\\n", "61 None None None \\\\\n", "62 None None None \\\\\n", "63 None None None \\\\\n", "64 None None None \\\\\n", "65 None None None \\\\\n", "66 None None None \\\\\n", "67 None None None \\\\\n", "68 None None None \\\\\n", "69 B None None \\\\\n", "70 None None None \\\\\n", "71 None None None \\\\\n", "72 None None None \\\\\n", "73 None 0 0 \\\\\n", "74 None 0 0 \\\\\n", "75 None 0 0 \\\\\n", "76 None None None \\\\\n", "77 None None None \\\\\n", "78 None None None \\\\\n", "79 None None None \\\\\n", "80 None None None \\\\\n", "81 None None None \\\\\n", "82 None 0 0 \\\\\n", "83 None None None \\\\\n", "84 None None None \\\\\n", "85 None None None \\\\\n", "86 None 6 0 \\\\\n", "87 None None None \\\\\n", "88 None 16 0 \\\\\n", "89 None None None \\\\\n", "90 None None None \\\\\n", "91 None 0 0 \\\\\n", "92 None 0 0 \\\\\n", "93 None None None \\\\\n", "94 None None None \\\\\n", "95 None None None \\\\\n", "96 None None None \\\\\n", "97 None None None \\\\\n", "98 None None None \\\\\n", "99 None 3 0 \\\\\n", "100 None None None \\\\\n", " AIRLINE_DELAY LATE_AIRCRAFT_DELAY WEATHER_DELAY \n", "1 None None None \n", "2 None None None \n", "3 None None None \n", "4 None None None \n", "5 197 92 0 \n", "6 None None None \n", "7 178 0 0 \n", "8 0 108 0 \n", "9 None None None \n", "10 None None None \n", "11 None None None \n", "12 None None None \n", "13 0 0 0 \n", "14 0 31 0 \n", "15 None None None \n", "16 None None None \n", "17 None None None \n", "18 6 0 0 \n", "19 None None None \n", "20 46 0 0 \n", "21 0 33 0 \n", "22 None None None \n", "23 None None None \n", "24 29 0 0 \n", "25 None None None \n", "26 91 0 0 \n", "27 None None None \n", "28 None None None \n", "29 20 0 0 \n", "30 None None None \n", "31 0 0 0 \n", "32 7 28 0 \n", "33 None None None \n", "34 None None None \n", "35 None None None \n", "36 0 0 0 \n", "37 None None None \n", "38 None None None \n", "39 0 0 0 \n", "40 None None None \n", "41 None None None \n", "42 None None None \n", "43 0 61 0 \n", "44 None None None \n", "45 24 0 0 \n", "46 9 0 0 \n", "47 22 159 0 \n", "48 None None None \n", "49 None None None \n", "50 None None None \n", "51 59 0 0 \n", "52 None None None \n", "53 None None None \n", "54 112 0 0 \n", "55 None None None \n", "56 None None None \n", "57 None None None \n", "58 10 0 0 \n", "59 None None None \n", "60 None None None \n", "61 None None None \n", "62 None None None \n", "63 None None None \n", "64 None None None \n", "65 None None None \n", "66 None None None \n", "67 None None None \n", "68 None None None \n", "69 None None None \n", "70 None None None \n", "71 None None None \n", "72 None None None \n", "73 0 38 0 \n", "74 0 46 0 \n", "75 0 23 0 \n", "76 None None None \n", "77 None None None \n", "78 None None None \n", "79 None None None \n", "80 None None None \n", "81 None None None \n", "82 54 0 0 \n", "83 None None None \n", "84 None None None \n", "85 None None None \n", "86 29 0 0 \n", "87 None None None \n", "88 6 10 0 \n", "89 None None None \n", "90 None None None \n", "91 0 276 0 \n", "92 23 0 0 \n", "93 None None None \n", "94 None None None \n", "95 None None None \n", "96 None None None \n", "97 None None None \n", "98 None None None \n", "99 21 0 0 \n", "100 None None None \n", "Rows: 1-100 | Columns: 31" ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Reading a csv file and naming the table flights_vertica\n", "flight_vertica = vp.read_csv('flights.csv',\n", " table_name = \"flight_vertica\")\n", "flight_vertica" ] }, { "cell_type": "code", "execution_count": 8, "id": "ee830fcc", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
Execution: 0.014s
" ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "
123
YEAR
Integer
123
MONTH
Integer
123
DAY
Integer
123
DAY_OF_WEEK
Integer
Abc
AIRLINE
Varchar(20)
123
FLIGHT_NUMBER
Integer
Abc
TAIL_NUMBER
Varchar(20)
Abc
ORIGIN_AIRPORT
Varchar(20)
Abc
DESTINATION_AIRPORT
Varchar(20)
123
SCHEDULED_DEPARTURE
Integer
123
DEPARTURE_TIME
Integer
123
DEPARTURE_DELAY
Integer
123
TAXI_OUT
Integer
123
WHEELS_OFF
Integer
123
SCHEDULED_TIME
Integer
123
ELAPSED_TIME
Integer
123
AIR_TIME
Integer
123
DISTANCE
Integer
123
WHEELS_ON
Integer
123
TAXI_IN
Integer
123
SCHEDULED_ARRIVAL
Integer
123
ARRIVAL_TIME
Integer
123
ARRIVAL_DELAY
Integer
123
DIVERTED
Integer
123
CANCELLED
Integer
Abc
CANCELLATION_REASON
Varchar(20)
123
AIR_SYSTEM_DELAY
Integer
123
SECURITY_DELAY
Integer
123
AIRLINE_DELAY
Integer
123
LATE_AIRCRAFT_DELAY
Integer
123
WEATHER_DELAY
Integer
12015114AA1N787AAJFKLAX900855-51791239040237824751230712301237700[null][null][null][null][null][null]
22015114AA2N795AALAXJFK900856-41691233529527124751643817351651-4400[null][null][null][null][null][null]
32015114AA3N798AAJFKLAX12301226-419124538038235824751543515501548-200[null][null][null][null][null][null]
42015114AA4N799AALAXJFK12201214-6231237330319284247520211220502033-1700[null][null][null][null][null][null]
52015114AA5N376AADFWHNL130517542892118155155264993784223461740224030000[null]110197920
62015114AA6N398AAOGGDFW1805[null][null][null][null]425[null][null]3711[null][null]510[null][null]01A[null][null][null][null][null]
72015114AA7N398AADFWOGG121515131782415375005174903711194731635195019500[null]17017800
82015114AA8N368AAHNLDFW1745193310815194844544642037846481151065910900[null]1001080
92015114AA9N792AAJFKLAX700649-112271138039736824751019710201026600[null][null][null][null][null][null]
102015114AA10N796AALAXJFK21502150014220430929427524755395559544-1500[null][null][null][null][null][null]
112015114AA12N792AALAXJFK11151114-1191133335321287247519201519501935-1500[null][null][null][null][null][null]
122015114AA14N5ERAAOGGLAX23002254-6122306305289267248653310605543-2200[null][null][null][null][null][null]
132015114AA15N783AAJFKSFO800755-557852400453388258612208114012284800[null]480000
142015114AA16N783AASFOJFK12451316311813343303333052586213910211521493400[null]300310
152015114AA17N019AAATLMIA700656-415711112106825948339852842-1000[null][null][null][null][null][null]
162015114AA19N786AAJFKLAX11001055-517111238037835424751406714201413-700[null][null][null][null][null][null]
172015114AA20N789AASFOJFK15001503319152232532830125862323823252331600[null][null][null][null][null][null]
182015114AA21N795AAJFKLAX1845185162919203854073682475222810221022382800[null]220600
192015114AA22N786AALAXJFK15151512-3231535320317277247523121723352329-600[null][null][null][null][null][null]
202015114AA23N371AADFWSFO755841463591623023319014641026894510344900[null]304600
212015114AA23N371AASFODFW10501123332811512102171781464164911162017004000[null]700330
222015114AA24N784AASFOJFK720714-62073434033230725861541516001546-1400[null][null][null][null][null][null]
232015114AA25N3AVAABOSLAX900857-31591239540538126111233912351242700[null][null][null][null][null][null]
242015114AA27N5DUAADFWLAS1850192939161945175165141105520068194520142900[null]002900
252015114AA28N358AALAXMIA15001456-4191515290305284234222592225023011100[null][null][null][null][null][null]
262015114AA29N455AAONTDFW122513569125142117521714911881850431720193313300[null]4209100
272015114AA29N568AADFWONT1035[null][null][null][null]190[null][null]1188[null][null]1145[null][null]01B[null][null][null][null][null]
282015114AA30N795AALAXJFK23302326-423234932029927024757196750725-2500[null][null][null][null][null][null]
292015114AA31N5ESAALAXHNL8058282312840365362346255612264121012302000[null]002000
302015114AA32N794AALAXJFK141514249141438325315288247522261322402239-100[null][null][null][null][null][null]
312015114AA35N3ALAADFWHDN11101109-1111120145178111769121156123513073200[null]320000
322015114AA35N3ALAAHDNDFW1320140040111411135130109769170010163517103500[null]007280
332015114AA36N3EKAASANDFW935928-713941170172140117114011914251420-500[null][null][null][null][null][null]
342015114AA37N483AADFWSAN21002103318212118518716611712207322052210500[null][null][null][null][null][null]
352015114AA40N3BYAADCAORD9559550910041301261116121055611051101-400[null][null][null][null][null][null]
362015114AA41N3KEAAORDSEA20552054-11621102702932361721230641232523472200[null]220000
372015114AA42N3GPAASEAORD850849-11690523522420117211426714451433-1200[null][null][null][null][null][null]
382015114AA43N3BNAADFWDTW1030[null][null][null][null]150[null][null]986[null][null]1400[null][null]01B[null][null][null][null][null]
392015114AA43N3KCAADTWDFW144514450191504180199163986164717164517041900[null]190000
402015114AA44N3AJAALASJFK810803-71782029527224822481528716051535-3000[null][null][null][null][null][null]
412015114AA45N3ELAAJFKLAS17291727-221174835534731822482006820242014-1000[null][null][null][null][null][null]
422015114AA46N3CSAASJCORD725725013738255253226182913241413401338-200[null][null][null][null][null][null]
432015114AA47N3KSAAORDSJC1915203580182053280261238182922515215522566100[null]000610
442015114AA48N3JXAASANDFW835830-513843175164141117113041013301314-1600[null][null][null][null][null][null]
452015114AA49N5ELAADFWSAN1935200631152021185178160117121013204021042400[null]002400
462015114AA50N570AASLCDFW81582493690016517913498912149120012232300[null]140900
472015114AA51N471AADFWSLC17252035190242059170161130989220971915221618100[null]00221590
482015114AA52N3LMAALAXDCA12251222-318124029027925323111953820152001-1400[null][null][null][null][null][null]
492015114AA53N3LHAADCALAX840838-21285036035033123111121711401128-1200[null][null][null][null][null][null]
502015114AA54N3CWAALASORD910906-41692221519317015141412714451419-2600[null][null][null][null][null][null]
512015114AA55N3JJAAORDLAS1820192464131937230225205151421027201021095900[null]005900
522015114AA58N5DFAAKOALAX1400140002214223103202762504205822211021201000[null][null][null][null][null][null]
532015114AA59N5DFAALAXKOA840837-31785435035233225041226312301229-100[null][null][null][null][null][null]
542015114AA60N372AADFWMIA175419551211520101601511331121232332134232611200[null]0011200
552015114AA61N850AAMIADFW715719413732199192167112191912934931-300[null][null][null][null][null][null]
562015114AA63N3ECAAMIAPHX19501955523201831730227119722249823072257-1000[null][null][null][null][null][null]
572015114AA64N3HXAADFWJFK102510411613105420018716913911443514451448300[null][null][null][null][null][null]
582015114AA65N3HLAAJFKDFW16151625101916442552942271391193148193020194900[null]3901000
592015114AA66N631AASJUJFK11511142-922120424524722115981445414561449-700[null][null][null][null][null][null]
602015114AA67N631AAJFKSJU160516292423165223521118215982054621002100000[null][null][null][null][null][null]
612015114AA68N5EGAASFOMIA70070331471732431729525851512815241520-400[null][null][null][null][null][null]
622015114AA69N3HTAAMIASFO18351836112184838635934225852130522012135-2600[null][null][null][null][null][null]
632015114AA70N5BTAASANDFW705659-622721180171137117111381212051150-1500[null][null][null][null][null][null]
642015114AA71N3GHAADFWSAN18051803-214181718017616011711857219051859-600[null][null][null][null][null][null]
652015114AA72N5EKAADFWMCO6006066186241451421209859244925928300[null][null][null][null][null][null]
662015114AA72N5EKAAMCODFW10201015-516103117517114398511541212151206-900[null][null][null][null][null][null]
672015114AA73N3FBAAPHLCLT1005957-8261023120108764491139612051145-2000[null][null][null][null][null][null]
682015114AA74N488AACLEDFW15551552-311160319018116110211744918051753-1200[null][null][null][null][null][null]
692015114AA74N505AADFWCLE1145[null][null][null][null]150[null][null]1021[null][null]1515[null][null]01B[null][null][null][null][null]
702015114AA75N3HPAAIADLAX18301822-817183936035833522882114621302120-1000[null][null][null][null][null][null]
712015114AA76N3HPAALAXIAD950946-418100428526524322881707417351711-2400[null][null][null][null][null][null]
722015114AA76N3HPAASFOLAX730723-71573885974933782733855900500[null][null][null][null][null][null]
732015114AA79N520AADFWSMF2140221939302249225224188143123576232533800[null]000380
742015114AA80N4XTAATUSDFW1655174146101751130130105813203615200520514600[null]000460
752015114AA81N4XCAADFWTUS22452308231323211401401238132445282300[null]000230
762015114AA83N4XBAAMCODFW735733-21674918017414698591512935927-800[null][null][null][null][null][null]
772015114AA84N3AXAABOSJFK940930-10159457569461871031810551039-1600[null][null][null][null][null][null]
782015114AA84N3AXAAJFKBOS7307344177517065441878354840839-100[null][null][null][null][null][null]
792015114AA85N785AAJFKSFO15301524-620154440038636125861845519101850-2000[null][null][null][null][null][null]
802015114AA86N3GTAAPDXORD705701-41571623523020817391244713001251-900[null][null][null][null][null][null]
812015114AA87N490AAORDRNO18451840-511185126522821316712024421102028-4200[null][null][null][null][null][null]
822015114AA89N3KVAAIAHMIA520618581963714113711196492878419355400[null]005400
832015114AA92N3KHAASANORD90090001591524021018917231424615001430-3000[null][null][null][null][null][null]
842015114AA93N3HHAAORDSAN1720180545101815255222210172319452193519471200[null][null][null][null][null][null]
852015114AA94N3ANAASANJFK755810151382332028126224461545616151551-2400[null][null][null][null][null][null]
862015114AA95N3ANAAJFKSAN1720174929181807380386363244621105204021153500[null]602900
872015114AA96N553AADFWSTL82083111218529594705501002395510051000[null][null][null][null][null][null]
882015114AA96N553AASTLDFW103510511615110611513198550124418123013023200[null]1606100
892015114AA97N3ENAADCADFW845842-314856215210185119211011111201112-800[null][null][null][null][null][null]
902015114AA99N3KMAAORDPDX15501553311160427525323917391803318251806-1900[null][null][null][null][null][null]
912015114AA102N376AAHNLDFW1920235627617134454454173784111011645112127600[null]0002760
922015114AA103N4XSAADFWMSY141014332313144680806244715485153015532300[null]002300
932015114AA103N4XSAAMSYDFW16101624148163210091744471746917501755500[null][null][null][null][null][null]
942015114AA110N3LRAASNAORD805801-47808230211194172613221013551332-2300[null][null][null][null][null][null]
952015114AA111N3LYAAORDSNA17201719-111173026023722217261912419401916-2400[null][null][null][null][null][null]
962015114AA112N857AADENMIA101510172191036224229199170915551115591606700[null][null][null][null][null][null]
972015114AA113N3GRAAMIADEN211521150212136269269235170923311323442344000[null][null][null][null][null][null]
982015114AA115N3CTAALAXMIA105103-21411728627625523428327851839-1200[null][null][null][null][null][null]
992015114AA116N383AAOGGDFW1955201621102026430433418371172457057292400[null]302100
1002015114AA117N793AAJFKLAX14451438-728150639038935524751801618151807-800[null][null][null][null][null][null]
Columns: 31
" ], "text/plain": [ " YEAR MONTH DAY DAY_OF_WEEK AIRLINE \\\\\n", "1 2015 1 1 4 AA \\\\\n", "2 2015 1 1 4 AA \\\\\n", "3 2015 1 1 4 AA \\\\\n", "4 2015 1 1 4 AA \\\\\n", "5 2015 1 1 4 AA \\\\\n", "6 2015 1 1 4 AA \\\\\n", "7 2015 1 1 4 AA \\\\\n", "8 2015 1 1 4 AA \\\\\n", "9 2015 1 1 4 AA \\\\\n", "10 2015 1 1 4 AA \\\\\n", "11 2015 1 1 4 AA \\\\\n", "12 2015 1 1 4 AA \\\\\n", "13 2015 1 1 4 AA \\\\\n", "14 2015 1 1 4 AA \\\\\n", "15 2015 1 1 4 AA \\\\\n", "16 2015 1 1 4 AA \\\\\n", "17 2015 1 1 4 AA \\\\\n", "18 2015 1 1 4 AA \\\\\n", "19 2015 1 1 4 AA \\\\\n", "20 2015 1 1 4 AA \\\\\n", "21 2015 1 1 4 AA \\\\\n", "22 2015 1 1 4 AA \\\\\n", "23 2015 1 1 4 AA \\\\\n", "24 2015 1 1 4 AA \\\\\n", "25 2015 1 1 4 AA \\\\\n", "26 2015 1 1 4 AA \\\\\n", "27 2015 1 1 4 AA \\\\\n", "28 2015 1 1 4 AA \\\\\n", "29 2015 1 1 4 AA \\\\\n", "30 2015 1 1 4 AA \\\\\n", "31 2015 1 1 4 AA \\\\\n", "32 2015 1 1 4 AA \\\\\n", "33 2015 1 1 4 AA \\\\\n", "34 2015 1 1 4 AA \\\\\n", "35 2015 1 1 4 AA \\\\\n", "36 2015 1 1 4 AA \\\\\n", "37 2015 1 1 4 AA \\\\\n", "38 2015 1 1 4 AA \\\\\n", "39 2015 1 1 4 AA \\\\\n", "40 2015 1 1 4 AA \\\\\n", "41 2015 1 1 4 AA \\\\\n", "42 2015 1 1 4 AA \\\\\n", "43 2015 1 1 4 AA \\\\\n", "44 2015 1 1 4 AA \\\\\n", "45 2015 1 1 4 AA \\\\\n", "46 2015 1 1 4 AA \\\\\n", "47 2015 1 1 4 AA \\\\\n", "48 2015 1 1 4 AA \\\\\n", "49 2015 1 1 4 AA \\\\\n", "50 2015 1 1 4 AA \\\\\n", "51 2015 1 1 4 AA \\\\\n", "52 2015 1 1 4 AA \\\\\n", "53 2015 1 1 4 AA \\\\\n", "54 2015 1 1 4 AA \\\\\n", "55 2015 1 1 4 AA \\\\\n", "56 2015 1 1 4 AA \\\\\n", "57 2015 1 1 4 AA \\\\\n", "58 2015 1 1 4 AA \\\\\n", "59 2015 1 1 4 AA \\\\\n", "60 2015 1 1 4 AA \\\\\n", "61 2015 1 1 4 AA \\\\\n", "62 2015 1 1 4 AA \\\\\n", "63 2015 1 1 4 AA \\\\\n", "64 2015 1 1 4 AA \\\\\n", "65 2015 1 1 4 AA \\\\\n", "66 2015 1 1 4 AA \\\\\n", "67 2015 1 1 4 AA \\\\\n", "68 2015 1 1 4 AA \\\\\n", "69 2015 1 1 4 AA \\\\\n", "70 2015 1 1 4 AA \\\\\n", "71 2015 1 1 4 AA \\\\\n", "72 2015 1 1 4 AA \\\\\n", "73 2015 1 1 4 AA \\\\\n", "74 2015 1 1 4 AA \\\\\n", "75 2015 1 1 4 AA \\\\\n", "76 2015 1 1 4 AA \\\\\n", "77 2015 1 1 4 AA \\\\\n", "78 2015 1 1 4 AA \\\\\n", "79 2015 1 1 4 AA \\\\\n", "80 2015 1 1 4 AA \\\\\n", "81 2015 1 1 4 AA \\\\\n", "82 2015 1 1 4 AA \\\\\n", "83 2015 1 1 4 AA \\\\\n", "84 2015 1 1 4 AA \\\\\n", "85 2015 1 1 4 AA \\\\\n", "86 2015 1 1 4 AA \\\\\n", "87 2015 1 1 4 AA \\\\\n", "88 2015 1 1 4 AA \\\\\n", "89 2015 1 1 4 AA \\\\\n", "90 2015 1 1 4 AA \\\\\n", "91 2015 1 1 4 AA \\\\\n", "92 2015 1 1 4 AA \\\\\n", "93 2015 1 1 4 AA \\\\\n", "94 2015 1 1 4 AA \\\\\n", "95 2015 1 1 4 AA \\\\\n", "96 2015 1 1 4 AA \\\\\n", "97 2015 1 1 4 AA \\\\\n", "98 2015 1 1 4 AA \\\\\n", "99 2015 1 1 4 AA \\\\\n", "100 2015 1 1 4 AA \\\\\n", " FLIGHT_NUMBER TAIL_NUMBER ORIGIN_AIRPORT DESTINATION_AIRPORT \\\\\n", "1 1 N787AA JFK LAX \\\\\n", "2 2 N795AA LAX JFK \\\\\n", "3 3 N798AA JFK LAX \\\\\n", "4 4 N799AA LAX JFK \\\\\n", "5 5 N376AA DFW HNL \\\\\n", "6 6 N398AA OGG DFW \\\\\n", "7 7 N398AA DFW OGG \\\\\n", "8 8 N368AA HNL DFW \\\\\n", "9 9 N792AA JFK LAX \\\\\n", "10 10 N796AA LAX JFK \\\\\n", "11 12 N792AA LAX JFK \\\\\n", "12 14 N5ERAA OGG LAX \\\\\n", "13 15 N783AA JFK SFO \\\\\n", "14 16 N783AA SFO JFK \\\\\n", "15 17 N019AA ATL MIA \\\\\n", "16 19 N786AA JFK LAX \\\\\n", "17 20 N789AA SFO JFK \\\\\n", "18 21 N795AA JFK LAX \\\\\n", "19 22 N786AA LAX JFK \\\\\n", "20 23 N371AA DFW SFO \\\\\n", "21 23 N371AA SFO DFW \\\\\n", "22 24 N784AA SFO JFK \\\\\n", "23 25 N3AVAA BOS LAX \\\\\n", "24 27 N5DUAA DFW LAS \\\\\n", "25 28 N358AA LAX MIA \\\\\n", "26 29 N455AA ONT DFW \\\\\n", "27 29 N568AA DFW ONT \\\\\n", "28 30 N795AA LAX JFK \\\\\n", "29 31 N5ESAA LAX HNL \\\\\n", "30 32 N794AA LAX JFK \\\\\n", "31 35 N3ALAA DFW HDN \\\\\n", "32 35 N3ALAA HDN DFW \\\\\n", "33 36 N3EKAA SAN DFW \\\\\n", "34 37 N483AA DFW SAN \\\\\n", "35 40 N3BYAA DCA ORD \\\\\n", "36 41 N3KEAA ORD SEA \\\\\n", "37 42 N3GPAA SEA ORD \\\\\n", "38 43 N3BNAA DFW DTW \\\\\n", "39 43 N3KCAA DTW DFW \\\\\n", "40 44 N3AJAA LAS JFK \\\\\n", "41 45 N3ELAA JFK LAS \\\\\n", "42 46 N3CSAA SJC ORD \\\\\n", "43 47 N3KSAA ORD SJC \\\\\n", "44 48 N3JXAA SAN DFW \\\\\n", "45 49 N5ELAA DFW SAN \\\\\n", "46 50 N570AA SLC DFW \\\\\n", "47 51 N471AA DFW SLC \\\\\n", "48 52 N3LMAA LAX DCA \\\\\n", "49 53 N3LHAA DCA LAX \\\\\n", "50 54 N3CWAA LAS ORD \\\\\n", "51 55 N3JJAA ORD LAS \\\\\n", "52 58 N5DFAA KOA LAX \\\\\n", "53 59 N5DFAA LAX KOA \\\\\n", "54 60 N372AA DFW MIA \\\\\n", "55 61 N850AA MIA DFW \\\\\n", "56 63 N3ECAA MIA PHX \\\\\n", "57 64 N3HXAA DFW JFK \\\\\n", "58 65 N3HLAA JFK DFW \\\\\n", "59 66 N631AA SJU JFK \\\\\n", "60 67 N631AA JFK SJU \\\\\n", "61 68 N5EGAA SFO MIA \\\\\n", "62 69 N3HTAA MIA SFO \\\\\n", "63 70 N5BTAA SAN DFW \\\\\n", "64 71 N3GHAA DFW SAN \\\\\n", "65 72 N5EKAA DFW MCO \\\\\n", "66 72 N5EKAA MCO DFW \\\\\n", "67 73 N3FBAA PHL CLT \\\\\n", "68 74 N488AA CLE DFW \\\\\n", "69 74 N505AA DFW CLE \\\\\n", "70 75 N3HPAA IAD LAX \\\\\n", "71 76 N3HPAA LAX IAD \\\\\n", "72 76 N3HPAA SFO LAX \\\\\n", "73 79 N520AA DFW SMF \\\\\n", "74 80 N4XTAA TUS DFW \\\\\n", "75 81 N4XCAA DFW TUS \\\\\n", "76 83 N4XBAA MCO DFW \\\\\n", "77 84 N3AXAA BOS JFK \\\\\n", "78 84 N3AXAA JFK BOS \\\\\n", "79 85 N785AA JFK SFO \\\\\n", "80 86 N3GTAA PDX ORD \\\\\n", "81 87 N490AA ORD RNO \\\\\n", "82 89 N3KVAA IAH MIA \\\\\n", "83 92 N3KHAA SAN ORD \\\\\n", "84 93 N3HHAA ORD SAN \\\\\n", "85 94 N3ANAA SAN JFK \\\\\n", "86 95 N3ANAA JFK SAN \\\\\n", "87 96 N553AA DFW STL \\\\\n", "88 96 N553AA STL DFW \\\\\n", "89 97 N3ENAA DCA DFW \\\\\n", "90 99 N3KMAA ORD PDX \\\\\n", "91 102 N376AA HNL DFW \\\\\n", "92 103 N4XSAA DFW MSY \\\\\n", "93 103 N4XSAA MSY DFW \\\\\n", "94 110 N3LRAA SNA ORD \\\\\n", "95 111 N3LYAA ORD SNA \\\\\n", "96 112 N857AA DEN MIA \\\\\n", "97 113 N3GRAA MIA DEN \\\\\n", "98 115 N3CTAA LAX MIA \\\\\n", "99 116 N383AA OGG DFW \\\\\n", "100 117 N793AA JFK LAX \\\\\n", " SCHEDULED_DEPARTURE DEPARTURE_TIME DEPARTURE_DELAY \\\\\n", "1 900 855 -5 \\\\\n", "2 900 856 -4 \\\\\n", "3 1230 1226 -4 \\\\\n", "4 1220 1214 -6 \\\\\n", "5 1305 1754 289 \\\\\n", "6 1805 None None \\\\\n", "7 1215 1513 178 \\\\\n", "8 1745 1933 108 \\\\\n", "9 700 649 -11 \\\\\n", "10 2150 2150 0 \\\\\n", "11 1115 1114 -1 \\\\\n", "12 2300 2254 -6 \\\\\n", "13 800 755 -5 \\\\\n", "14 1245 1316 31 \\\\\n", "15 700 656 -4 \\\\\n", "16 1100 1055 -5 \\\\\n", "17 1500 1503 3 \\\\\n", "18 1845 1851 6 \\\\\n", "19 1515 1512 -3 \\\\\n", "20 755 841 46 \\\\\n", "21 1050 1123 33 \\\\\n", "22 720 714 -6 \\\\\n", "23 900 857 -3 \\\\\n", "24 1850 1929 39 \\\\\n", "25 1500 1456 -4 \\\\\n", "26 1225 1356 91 \\\\\n", "27 1035 None None \\\\\n", "28 2330 2326 -4 \\\\\n", "29 805 828 23 \\\\\n", "30 1415 1424 9 \\\\\n", "31 1110 1109 -1 \\\\\n", "32 1320 1400 40 \\\\\n", "33 935 928 -7 \\\\\n", "34 2100 2103 3 \\\\\n", "35 955 955 0 \\\\\n", "36 2055 2054 -1 \\\\\n", "37 850 849 -1 \\\\\n", "38 1030 None None \\\\\n", "39 1445 1445 0 \\\\\n", "40 810 803 -7 \\\\\n", "41 1729 1727 -2 \\\\\n", "42 725 725 0 \\\\\n", "43 1915 2035 80 \\\\\n", "44 835 830 -5 \\\\\n", "45 1935 2006 31 \\\\\n", "46 815 824 9 \\\\\n", "47 1725 2035 190 \\\\\n", "48 1225 1222 -3 \\\\\n", "49 840 838 -2 \\\\\n", "50 910 906 -4 \\\\\n", "51 1820 1924 64 \\\\\n", "52 1400 1400 0 \\\\\n", "53 840 837 -3 \\\\\n", "54 1754 1955 121 \\\\\n", "55 715 719 4 \\\\\n", "56 1950 1955 5 \\\\\n", "57 1025 1041 16 \\\\\n", "58 1615 1625 10 \\\\\n", "59 1151 1142 -9 \\\\\n", "60 1605 1629 24 \\\\\n", "61 700 703 3 \\\\\n", "62 1835 1836 1 \\\\\n", "63 705 659 -6 \\\\\n", "64 1805 1803 -2 \\\\\n", "65 600 606 6 \\\\\n", "66 1020 1015 -5 \\\\\n", "67 1005 957 -8 \\\\\n", "68 1555 1552 -3 \\\\\n", "69 1145 None None \\\\\n", "70 1830 1822 -8 \\\\\n", "71 950 946 -4 \\\\\n", "72 730 723 -7 \\\\\n", "73 2140 2219 39 \\\\\n", "74 1655 1741 46 \\\\\n", "75 2245 2308 23 \\\\\n", "76 735 733 -2 \\\\\n", "77 940 930 -10 \\\\\n", "78 730 734 4 \\\\\n", "79 1530 1524 -6 \\\\\n", "80 705 701 -4 \\\\\n", "81 1845 1840 -5 \\\\\n", "82 520 618 58 \\\\\n", "83 900 900 0 \\\\\n", "84 1720 1805 45 \\\\\n", "85 755 810 15 \\\\\n", "86 1720 1749 29 \\\\\n", "87 820 831 11 \\\\\n", "88 1035 1051 16 \\\\\n", "89 845 842 -3 \\\\\n", "90 1550 1553 3 \\\\\n", "91 1920 2356 276 \\\\\n", "92 1410 1433 23 \\\\\n", "93 1610 1624 14 \\\\\n", "94 805 801 -4 \\\\\n", "95 1720 1719 -1 \\\\\n", "96 1015 1017 2 \\\\\n", "97 2115 2115 0 \\\\\n", "98 105 103 -2 \\\\\n", "99 1955 2016 21 \\\\\n", "100 1445 1438 -7 \\\\\n", " TAXI_OUT WHEELS_OFF SCHEDULED_TIME ELAPSED_TIME \\\\\n", "1 17 912 390 402 \\\\\n", "2 16 912 335 295 \\\\\n", "3 19 1245 380 382 \\\\\n", "4 23 1237 330 319 \\\\\n", "5 21 1815 515 526 \\\\\n", "6 None None 425 None \\\\\n", "7 24 1537 500 517 \\\\\n", "8 15 1948 445 446 \\\\\n", "9 22 711 380 397 \\\\\n", "10 14 2204 309 294 \\\\\n", "11 19 1133 335 321 \\\\\n", "12 12 2306 305 289 \\\\\n", "13 57 852 400 453 \\\\\n", "14 18 1334 330 333 \\\\\n", "15 15 711 112 106 \\\\\n", "16 17 1112 380 378 \\\\\n", "17 19 1522 325 328 \\\\\n", "18 29 1920 385 407 \\\\\n", "19 23 1535 320 317 \\\\\n", "20 35 916 230 233 \\\\\n", "21 28 1151 210 217 \\\\\n", "22 20 734 340 332 \\\\\n", "23 15 912 395 405 \\\\\n", "24 16 1945 175 165 \\\\\n", "25 19 1515 290 305 \\\\\n", "26 25 1421 175 217 \\\\\n", "27 None None 190 None \\\\\n", "28 23 2349 320 299 \\\\\n", "29 12 840 365 362 \\\\\n", "30 14 1438 325 315 \\\\\n", "31 11 1120 145 178 \\\\\n", "32 11 1411 135 130 \\\\\n", "33 13 941 170 172 \\\\\n", "34 18 2121 185 187 \\\\\n", "35 9 1004 130 126 \\\\\n", "36 16 2110 270 293 \\\\\n", "37 16 905 235 224 \\\\\n", "38 None None 150 None \\\\\n", "39 19 1504 180 199 \\\\\n", "40 17 820 295 272 \\\\\n", "41 21 1748 355 347 \\\\\n", "42 13 738 255 253 \\\\\n", "43 18 2053 280 261 \\\\\n", "44 13 843 175 164 \\\\\n", "45 15 2021 185 178 \\\\\n", "46 36 900 165 179 \\\\\n", "47 24 2059 170 161 \\\\\n", "48 18 1240 290 279 \\\\\n", "49 12 850 360 350 \\\\\n", "50 16 922 215 193 \\\\\n", "51 13 1937 230 225 \\\\\n", "52 22 1422 310 320 \\\\\n", "53 17 854 350 352 \\\\\n", "54 15 2010 160 151 \\\\\n", "55 13 732 199 192 \\\\\n", "56 23 2018 317 302 \\\\\n", "57 13 1054 200 187 \\\\\n", "58 19 1644 255 294 \\\\\n", "59 22 1204 245 247 \\\\\n", "60 23 1652 235 211 \\\\\n", "61 14 717 324 317 \\\\\n", "62 12 1848 386 359 \\\\\n", "63 22 721 180 171 \\\\\n", "64 14 1817 180 176 \\\\\n", "65 18 624 145 142 \\\\\n", "66 16 1031 175 171 \\\\\n", "67 26 1023 120 108 \\\\\n", "68 11 1603 190 181 \\\\\n", "69 None None 150 None \\\\\n", "70 17 1839 360 358 \\\\\n", "71 18 1004 285 265 \\\\\n", "72 15 738 85 97 \\\\\n", "73 30 2249 225 224 \\\\\n", "74 10 1751 130 130 \\\\\n", "75 13 2321 140 140 \\\\\n", "76 16 749 180 174 \\\\\n", "77 15 945 75 69 \\\\\n", "78 17 751 70 65 \\\\\n", "79 20 1544 400 386 \\\\\n", "80 15 716 235 230 \\\\\n", "81 11 1851 265 228 \\\\\n", "82 19 637 141 137 \\\\\n", "83 15 915 240 210 \\\\\n", "84 10 1815 255 222 \\\\\n", "85 13 823 320 281 \\\\\n", "86 18 1807 380 386 \\\\\n", "87 21 852 95 94 \\\\\n", "88 15 1106 115 131 \\\\\n", "89 14 856 215 210 \\\\\n", "90 11 1604 275 253 \\\\\n", "91 17 13 445 445 \\\\\n", "92 13 1446 80 80 \\\\\n", "93 8 1632 100 91 \\\\\n", "94 7 808 230 211 \\\\\n", "95 11 1730 260 237 \\\\\n", "96 19 1036 224 229 \\\\\n", "97 21 2136 269 269 \\\\\n", "98 14 117 286 276 \\\\\n", "99 10 2026 430 433 \\\\\n", "100 28 1506 390 389 \\\\\n", " AIR_TIME DISTANCE WHEELS_ON TAXI_IN SCHEDULED_ARRIVAL \\\\\n", "1 378 2475 1230 7 1230 \\\\\n", "2 271 2475 1643 8 1735 \\\\\n", "3 358 2475 1543 5 1550 \\\\\n", "4 284 2475 2021 12 2050 \\\\\n", "5 499 3784 2234 6 1740 \\\\\n", "6 None 3711 None None 510 \\\\\n", "7 490 3711 1947 3 1635 \\\\\n", "8 420 3784 648 11 510 \\\\\n", "9 368 2475 1019 7 1020 \\\\\n", "10 275 2475 539 5 559 \\\\\n", "11 287 2475 1920 15 1950 \\\\\n", "12 267 2486 533 10 605 \\\\\n", "13 388 2586 1220 8 1140 \\\\\n", "14 305 2586 2139 10 2115 \\\\\n", "15 82 594 833 9 852 \\\\\n", "16 354 2475 1406 7 1420 \\\\\n", "17 301 2586 2323 8 2325 \\\\\n", "18 368 2475 2228 10 2210 \\\\\n", "19 277 2475 2312 17 2335 \\\\\n", "20 190 1464 1026 8 945 \\\\\n", "21 178 1464 1649 11 1620 \\\\\n", "22 307 2586 1541 5 1600 \\\\\n", "23 381 2611 1233 9 1235 \\\\\n", "24 141 1055 2006 8 1945 \\\\\n", "25 284 2342 2259 2 2250 \\\\\n", "26 149 1188 1850 43 1720 \\\\\n", "27 None 1188 None None 1145 \\\\\n", "28 270 2475 719 6 750 \\\\\n", "29 346 2556 1226 4 1210 \\\\\n", "30 288 2475 2226 13 2240 \\\\\n", "31 111 769 1211 56 1235 \\\\\n", "32 109 769 1700 10 1635 \\\\\n", "33 140 1171 1401 19 1425 \\\\\n", "34 166 1171 2207 3 2205 \\\\\n", "35 111 612 1055 6 1105 \\\\\n", "36 236 1721 2306 41 2325 \\\\\n", "37 201 1721 1426 7 1445 \\\\\n", "38 None 986 None None 1400 \\\\\n", "39 163 986 1647 17 1645 \\\\\n", "40 248 2248 1528 7 1605 \\\\\n", "41 318 2248 2006 8 2024 \\\\\n", "42 226 1829 1324 14 1340 \\\\\n", "43 238 1829 2251 5 2155 \\\\\n", "44 141 1171 1304 10 1330 \\\\\n", "45 160 1171 2101 3 2040 \\\\\n", "46 134 989 1214 9 1200 \\\\\n", "47 130 989 2209 7 1915 \\\\\n", "48 253 2311 1953 8 2015 \\\\\n", "49 331 2311 1121 7 1140 \\\\\n", "50 170 1514 1412 7 1445 \\\\\n", "51 205 1514 2102 7 2010 \\\\\n", "52 276 2504 2058 22 2110 \\\\\n", "53 332 2504 1226 3 1230 \\\\\n", "54 133 1121 2323 3 2134 \\\\\n", "55 167 1121 919 12 934 \\\\\n", "56 271 1972 2249 8 2307 \\\\\n", "57 169 1391 1443 5 1445 \\\\\n", "58 227 1391 1931 48 1930 \\\\\n", "59 221 1598 1445 4 1456 \\\\\n", "60 182 1598 2054 6 2100 \\\\\n", "61 295 2585 1512 8 1524 \\\\\n", "62 342 2585 2130 5 2201 \\\\\n", "63 137 1171 1138 12 1205 \\\\\n", "64 160 1171 1857 2 1905 \\\\\n", "65 120 985 924 4 925 \\\\\n", "66 143 985 1154 12 1215 \\\\\n", "67 76 449 1139 6 1205 \\\\\n", "68 161 1021 1744 9 1805 \\\\\n", "69 None 1021 None None 1515 \\\\\n", "70 335 2288 2114 6 2130 \\\\\n", "71 243 2288 1707 4 1735 \\\\\n", "72 49 337 827 33 855 \\\\\n", "73 188 1431 2357 6 2325 \\\\\n", "74 105 813 2036 15 2005 \\\\\n", "75 123 813 24 4 5 \\\\\n", "76 146 985 915 12 935 \\\\\n", "77 46 187 1031 8 1055 \\\\\n", "78 44 187 835 4 840 \\\\\n", "79 361 2586 1845 5 1910 \\\\\n", "80 208 1739 1244 7 1300 \\\\\n", "81 213 1671 2024 4 2110 \\\\\n", "82 111 964 928 7 841 \\\\\n", "83 189 1723 1424 6 1500 \\\\\n", "84 210 1723 1945 2 1935 \\\\\n", "85 262 2446 1545 6 1615 \\\\\n", "86 363 2446 2110 5 2040 \\\\\n", "87 70 550 1002 3 955 \\\\\n", "88 98 550 1244 18 1230 \\\\\n", "89 185 1192 1101 11 1120 \\\\\n", "90 239 1739 1803 3 1825 \\\\\n", "91 417 3784 1110 11 645 \\\\\n", "92 62 447 1548 5 1530 \\\\\n", "93 74 447 1746 9 1750 \\\\\n", "94 194 1726 1322 10 1355 \\\\\n", "95 222 1726 1912 4 1940 \\\\\n", "96 199 1709 1555 11 1559 \\\\\n", "97 235 1709 2331 13 2344 \\\\\n", "98 255 2342 832 7 851 \\\\\n", "99 418 3711 724 5 705 \\\\\n", "100 355 2475 1801 6 1815 \\\\\n", " ARRIVAL_TIME ARRIVAL_DELAY DIVERTED CANCELLED \\\\\n", "1 1237 7 0 0 \\\\\n", "2 1651 -44 0 0 \\\\\n", "3 1548 -2 0 0 \\\\\n", "4 2033 -17 0 0 \\\\\n", "5 2240 300 0 0 \\\\\n", "6 None None 0 1 \\\\\n", "7 1950 195 0 0 \\\\\n", "8 659 109 0 0 \\\\\n", "9 1026 6 0 0 \\\\\n", "10 544 -15 0 0 \\\\\n", "11 1935 -15 0 0 \\\\\n", "12 543 -22 0 0 \\\\\n", "13 1228 48 0 0 \\\\\n", "14 2149 34 0 0 \\\\\n", "15 842 -10 0 0 \\\\\n", "16 1413 -7 0 0 \\\\\n", "17 2331 6 0 0 \\\\\n", "18 2238 28 0 0 \\\\\n", "19 2329 -6 0 0 \\\\\n", "20 1034 49 0 0 \\\\\n", "21 1700 40 0 0 \\\\\n", "22 1546 -14 0 0 \\\\\n", "23 1242 7 0 0 \\\\\n", "24 2014 29 0 0 \\\\\n", "25 2301 11 0 0 \\\\\n", "26 1933 133 0 0 \\\\\n", "27 None None 0 1 \\\\\n", "28 725 -25 0 0 \\\\\n", "29 1230 20 0 0 \\\\\n", "30 2239 -1 0 0 \\\\\n", "31 1307 32 0 0 \\\\\n", "32 1710 35 0 0 \\\\\n", "33 1420 -5 0 0 \\\\\n", "34 2210 5 0 0 \\\\\n", "35 1101 -4 0 0 \\\\\n", "36 2347 22 0 0 \\\\\n", "37 1433 -12 0 0 \\\\\n", "38 None None 0 1 \\\\\n", "39 1704 19 0 0 \\\\\n", "40 1535 -30 0 0 \\\\\n", "41 2014 -10 0 0 \\\\\n", "42 1338 -2 0 0 \\\\\n", "43 2256 61 0 0 \\\\\n", "44 1314 -16 0 0 \\\\\n", "45 2104 24 0 0 \\\\\n", "46 1223 23 0 0 \\\\\n", "47 2216 181 0 0 \\\\\n", "48 2001 -14 0 0 \\\\\n", "49 1128 -12 0 0 \\\\\n", "50 1419 -26 0 0 \\\\\n", "51 2109 59 0 0 \\\\\n", "52 2120 10 0 0 \\\\\n", "53 1229 -1 0 0 \\\\\n", "54 2326 112 0 0 \\\\\n", "55 931 -3 0 0 \\\\\n", "56 2257 -10 0 0 \\\\\n", "57 1448 3 0 0 \\\\\n", "58 2019 49 0 0 \\\\\n", "59 1449 -7 0 0 \\\\\n", "60 2100 0 0 0 \\\\\n", "61 1520 -4 0 0 \\\\\n", "62 2135 -26 0 0 \\\\\n", "63 1150 -15 0 0 \\\\\n", "64 1859 -6 0 0 \\\\\n", "65 928 3 0 0 \\\\\n", "66 1206 -9 0 0 \\\\\n", "67 1145 -20 0 0 \\\\\n", "68 1753 -12 0 0 \\\\\n", "69 None None 0 1 \\\\\n", "70 2120 -10 0 0 \\\\\n", "71 1711 -24 0 0 \\\\\n", "72 900 5 0 0 \\\\\n", "73 3 38 0 0 \\\\\n", "74 2051 46 0 0 \\\\\n", "75 28 23 0 0 \\\\\n", "76 927 -8 0 0 \\\\\n", "77 1039 -16 0 0 \\\\\n", "78 839 -1 0 0 \\\\\n", "79 1850 -20 0 0 \\\\\n", "80 1251 -9 0 0 \\\\\n", "81 2028 -42 0 0 \\\\\n", "82 935 54 0 0 \\\\\n", "83 1430 -30 0 0 \\\\\n", "84 1947 12 0 0 \\\\\n", "85 1551 -24 0 0 \\\\\n", "86 2115 35 0 0 \\\\\n", "87 1005 10 0 0 \\\\\n", "88 1302 32 0 0 \\\\\n", "89 1112 -8 0 0 \\\\\n", "90 1806 -19 0 0 \\\\\n", "91 1121 276 0 0 \\\\\n", "92 1553 23 0 0 \\\\\n", "93 1755 5 0 0 \\\\\n", "94 1332 -23 0 0 \\\\\n", "95 1916 -24 0 0 \\\\\n", "96 1606 7 0 0 \\\\\n", "97 2344 0 0 0 \\\\\n", "98 839 -12 0 0 \\\\\n", "99 729 24 0 0 \\\\\n", "100 1807 -8 0 0 \\\\\n", " CANCELLATION_REASON AIR_SYSTEM_DELAY SECURITY_DELAY \\\\\n", "1 None None None \\\\\n", "2 None None None \\\\\n", "3 None None None \\\\\n", "4 None None None \\\\\n", "5 None 11 0 \\\\\n", "6 A None None \\\\\n", "7 None 17 0 \\\\\n", "8 None 1 0 \\\\\n", "9 None None None \\\\\n", "10 None None None \\\\\n", "11 None None None \\\\\n", "12 None None None \\\\\n", "13 None 48 0 \\\\\n", "14 None 3 0 \\\\\n", "15 None None None \\\\\n", "16 None None None \\\\\n", "17 None None None \\\\\n", "18 None 22 0 \\\\\n", "19 None None None \\\\\n", "20 None 3 0 \\\\\n", "21 None 7 0 \\\\\n", "22 None None None \\\\\n", "23 None None None \\\\\n", "24 None 0 0 \\\\\n", "25 None None None \\\\\n", "26 None 42 0 \\\\\n", "27 B None None \\\\\n", "28 None None None \\\\\n", "29 None 0 0 \\\\\n", "30 None None None \\\\\n", "31 None 32 0 \\\\\n", "32 None 0 0 \\\\\n", "33 None None None \\\\\n", "34 None None None \\\\\n", "35 None None None \\\\\n", "36 None 22 0 \\\\\n", "37 None None None \\\\\n", "38 B None None \\\\\n", "39 None 19 0 \\\\\n", "40 None None None \\\\\n", "41 None None None \\\\\n", "42 None None None \\\\\n", "43 None 0 0 \\\\\n", "44 None None None \\\\\n", "45 None 0 0 \\\\\n", "46 None 14 0 \\\\\n", "47 None 0 0 \\\\\n", "48 None None None \\\\\n", "49 None None None \\\\\n", "50 None None None \\\\\n", "51 None 0 0 \\\\\n", "52 None None None \\\\\n", "53 None None None \\\\\n", "54 None 0 0 \\\\\n", "55 None None None \\\\\n", "56 None None None \\\\\n", "57 None None None \\\\\n", "58 None 39 0 \\\\\n", "59 None None None \\\\\n", "60 None None None \\\\\n", "61 None None None \\\\\n", "62 None None None \\\\\n", "63 None None None \\\\\n", "64 None None None \\\\\n", "65 None None None \\\\\n", "66 None None None \\\\\n", "67 None None None \\\\\n", "68 None None None \\\\\n", "69 B None None \\\\\n", "70 None None None \\\\\n", "71 None None None \\\\\n", "72 None None None \\\\\n", "73 None 0 0 \\\\\n", "74 None 0 0 \\\\\n", "75 None 0 0 \\\\\n", "76 None None None \\\\\n", "77 None None None \\\\\n", "78 None None None \\\\\n", "79 None None None \\\\\n", "80 None None None \\\\\n", "81 None None None \\\\\n", "82 None 0 0 \\\\\n", "83 None None None \\\\\n", "84 None None None \\\\\n", "85 None None None \\\\\n", "86 None 6 0 \\\\\n", "87 None None None \\\\\n", "88 None 16 0 \\\\\n", "89 None None None \\\\\n", "90 None None None \\\\\n", "91 None 0 0 \\\\\n", "92 None 0 0 \\\\\n", "93 None None None \\\\\n", "94 None None None \\\\\n", "95 None None None \\\\\n", "96 None None None \\\\\n", "97 None None None \\\\\n", "98 None None None \\\\\n", "99 None 3 0 \\\\\n", "100 None None None \\\\\n", " AIRLINE_DELAY LATE_AIRCRAFT_DELAY WEATHER_DELAY \n", "1 None None None \n", "2 None None None \n", "3 None None None \n", "4 None None None \n", "5 197 92 0 \n", "6 None None None \n", "7 178 0 0 \n", "8 0 108 0 \n", "9 None None None \n", "10 None None None \n", "11 None None None \n", "12 None None None \n", "13 0 0 0 \n", "14 0 31 0 \n", "15 None None None \n", "16 None None None \n", "17 None None None \n", "18 6 0 0 \n", "19 None None None \n", "20 46 0 0 \n", "21 0 33 0 \n", "22 None None None \n", "23 None None None \n", "24 29 0 0 \n", "25 None None None \n", "26 91 0 0 \n", "27 None None None \n", "28 None None None \n", "29 20 0 0 \n", "30 None None None \n", "31 0 0 0 \n", "32 7 28 0 \n", "33 None None None \n", "34 None None None \n", "35 None None None \n", "36 0 0 0 \n", "37 None None None \n", "38 None None None \n", "39 0 0 0 \n", "40 None None None \n", "41 None None None \n", "42 None None None \n", "43 0 61 0 \n", "44 None None None \n", "45 24 0 0 \n", "46 9 0 0 \n", "47 22 159 0 \n", "48 None None None \n", "49 None None None \n", "50 None None None \n", "51 59 0 0 \n", "52 None None None \n", "53 None None None \n", "54 112 0 0 \n", "55 None None None \n", "56 None None None \n", "57 None None None \n", "58 10 0 0 \n", "59 None None None \n", "60 None None None \n", "61 None None None \n", "62 None None None \n", "63 None None None \n", "64 None None None \n", "65 None None None \n", "66 None None None \n", "67 None None None \n", "68 None None None \n", "69 None None None \n", "70 None None None \n", "71 None None None \n", "72 None None None \n", "73 0 38 0 \n", "74 0 46 0 \n", "75 0 23 0 \n", "76 None None None \n", "77 None None None \n", "78 None None None \n", "79 None None None \n", "80 None None None \n", "81 None None None \n", "82 54 0 0 \n", "83 None None None \n", "84 None None None \n", "85 None None None \n", "86 29 0 0 \n", "87 None None None \n", "88 6 10 0 \n", "89 None None None \n", "90 None None None \n", "91 0 276 0 \n", "92 23 0 0 \n", "93 None None None \n", "94 None None None \n", "95 None None None \n", "96 None None None \n", "97 None None None \n", "98 None None None \n", "99 21 0 0 \n", "100 None None None \n", "Columns: 31" ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "/* Fetch all the data from the table flight_vertica. */\n", "SELECT * FROM flight_vertica;" ] }, { "cell_type": "markdown", "id": "70ef2391", "metadata": { "tags": [] }, "source": [ "### Joins and Queries Across Multiple Databases" ] }, { "cell_type": "markdown", "id": "38b45d4e", "metadata": {}, "source": [ "Now we can run queries that execute through multiple sources.\n", "\n", "Let's try to find the TAIL_NUMBER and Departing City for all the flights by joining the two tables:\n", "
    \n", "
  • flight_vertica (stored in Vertica)
    • \n", "
  • airports (stored in MySQL)
    • \n", "
" ] }, { "cell_type": "code", "execution_count": 9, "id": "1613670e", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
Execution: 0.11s
" ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "
Abc
TAIL_NUMBER
Varchar(20)
Abc
Departing_City
Varchar(60)
1N787AANew York
2N795AALos Angeles
3N798AANew York
4N799AALos Angeles
5N376AADallas-Fort Worth
6N398AAKahului
7N398AADallas-Fort Worth
8N368AAHonolulu
9N792AANew York
10N796AALos Angeles
11N792AALos Angeles
12N5ERAAKahului
13N783AANew York
14N783AASan Francisco
15N019AAAtlanta
16N786AANew York
17N789AASan Francisco
18N795AANew York
19N786AALos Angeles
20N371AADallas-Fort Worth
21N371AASan Francisco
22N784AASan Francisco
23N3AVAABoston
24N5DUAADallas-Fort Worth
25N358AALos Angeles
26N455AAOntario
27N568AADallas-Fort Worth
28N795AALos Angeles
29N5ESAALos Angeles
30N794AALos Angeles
31N3ALAADallas-Fort Worth
32N3ALAAHayden
33N3EKAASan Diego
34N483AADallas-Fort Worth
35N3BYAAArlington
36N3KEAAChicago
37N3GPAASeattle
38N3BNAADallas-Fort Worth
39N3KCAADetroit
40N3AJAALas Vegas
41N3ELAANew York
42N3CSAASan Jose
43N3KSAAChicago
44N3JXAASan Diego
45N5ELAADallas-Fort Worth
46N570AASalt Lake City
47N471AADallas-Fort Worth
48N3LMAALos Angeles
49N3LHAAArlington
50N3CWAALas Vegas
51N3JJAAChicago
52N5DFAAKailua/Kona
53N5DFAALos Angeles
54N372AADallas-Fort Worth
55N850AAMiami
56N3ECAAMiami
57N3HXAADallas-Fort Worth
58N3HLAANew York
59N631AASan Juan
60N631AANew York
61N5EGAASan Francisco
62N3HTAAMiami
63N5BTAASan Diego
64N3GHAADallas-Fort Worth
65N5EKAADallas-Fort Worth
66N5EKAAOrlando
67N3FBAAPhiladelphia
68N488AACleveland
69N505AADallas-Fort Worth
70N3HPAAChantilly
71N3HPAALos Angeles
72N3HPAASan Francisco
73N520AADallas-Fort Worth
74N4XTAATucson
75N4XCAADallas-Fort Worth
76N4XBAAOrlando
77N3AXAABoston
78N3AXAANew York
79N785AANew York
80N3GTAAPortland
81N490AAChicago
82N3KVAAHouston
83N3KHAASan Diego
84N3HHAAChicago
85N3ANAASan Diego
86N3ANAANew York
87N553AADallas-Fort Worth
88N553AASt Louis
89N3ENAAArlington
90N3KMAAChicago
91N376AAHonolulu
92N4XSAADallas-Fort Worth
93N4XSAANew Orleans
94N3LRAASanta Ana
95N3LYAAChicago
96N857AADenver
97N3GRAAMiami
98N3CTAALos Angeles
99N383AAKahului
100N793AANew York
Columns: 2
" ], "text/plain": [ " TAIL_NUMBER Departing_City \n", "1 N787AA New York \n", "2 N795AA Los Angeles \n", "3 N798AA New York \n", "4 N799AA Los Angeles \n", "5 N376AA Dallas-Fort Worth \n", "6 N398AA Kahului \n", "7 N398AA Dallas-Fort Worth \n", "8 N368AA Honolulu \n", "9 N792AA New York \n", "10 N796AA Los Angeles \n", "11 N792AA Los Angeles \n", "12 N5ERAA Kahului \n", "13 N783AA New York \n", "14 N783AA San Francisco \n", "15 N019AA Atlanta \n", "16 N786AA New York \n", "17 N789AA San Francisco \n", "18 N795AA New York \n", "19 N786AA Los Angeles \n", "20 N371AA Dallas-Fort Worth \n", "21 N371AA San Francisco \n", "22 N784AA San Francisco \n", "23 N3AVAA Boston \n", "24 N5DUAA Dallas-Fort Worth \n", "25 N358AA Los Angeles \n", "26 N455AA Ontario \n", "27 N568AA Dallas-Fort Worth \n", "28 N795AA Los Angeles \n", "29 N5ESAA Los Angeles \n", "30 N794AA Los Angeles \n", "31 N3ALAA Dallas-Fort Worth \n", "32 N3ALAA Hayden \n", "33 N3EKAA San Diego \n", "34 N483AA Dallas-Fort Worth \n", "35 N3BYAA Arlington \n", "36 N3KEAA Chicago \n", "37 N3GPAA Seattle \n", "38 N3BNAA Dallas-Fort Worth \n", "39 N3KCAA Detroit \n", "40 N3AJAA Las Vegas \n", "41 N3ELAA New York \n", "42 N3CSAA San Jose \n", "43 N3KSAA Chicago \n", "44 N3JXAA San Diego \n", "45 N5ELAA Dallas-Fort Worth \n", "46 N570AA Salt Lake City \n", "47 N471AA Dallas-Fort Worth \n", "48 N3LMAA Los Angeles \n", "49 N3LHAA Arlington \n", "50 N3CWAA Las Vegas \n", "51 N3JJAA Chicago \n", "52 N5DFAA Kailua/Kona \n", "53 N5DFAA Los Angeles \n", "54 N372AA Dallas-Fort Worth \n", "55 N850AA Miami \n", "56 N3ECAA Miami \n", "57 N3HXAA Dallas-Fort Worth \n", "58 N3HLAA New York \n", "59 N631AA San Juan \n", "60 N631AA New York \n", "61 N5EGAA San Francisco \n", "62 N3HTAA Miami \n", "63 N5BTAA San Diego \n", "64 N3GHAA Dallas-Fort Worth \n", "65 N5EKAA Dallas-Fort Worth \n", "66 N5EKAA Orlando \n", "67 N3FBAA Philadelphia \n", "68 N488AA Cleveland \n", "69 N505AA Dallas-Fort Worth \n", "70 N3HPAA Chantilly \n", "71 N3HPAA Los Angeles \n", "72 N3HPAA San Francisco \n", "73 N520AA Dallas-Fort Worth \n", "74 N4XTAA Tucson \n", "75 N4XCAA Dallas-Fort Worth \n", "76 N4XBAA Orlando \n", "77 N3AXAA Boston \n", "78 N3AXAA New York \n", "79 N785AA New York \n", "80 N3GTAA Portland \n", "81 N490AA Chicago \n", "82 N3KVAA Houston \n", "83 N3KHAA San Diego \n", "84 N3HHAA Chicago \n", "85 N3ANAA San Diego \n", "86 N3ANAA New York \n", "87 N553AA Dallas-Fort Worth \n", "88 N553AA St Louis \n", "89 N3ENAA Arlington \n", "90 N3KMAA Chicago \n", "91 N376AA Honolulu \n", "92 N4XSAA Dallas-Fort Worth \n", "93 N4XSAA New Orleans \n", "94 N3LRAA Santa Ana \n", "95 N3LYAA Chicago \n", "96 N857AA Denver \n", "97 N3GRAA Miami \n", "98 N3CTAA Los Angeles \n", "99 N383AA Kahului \n", "100 N793AA New York \n", "Columns: 2" ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "/* Fetch TAIL_NUMBER and CITY after Joining the flight_vertica table with airports table in MySQL database. */\n", "SELECT flight_vertica.TAIL_NUMBER, airports.CITY AS Departing_City\n", "FROM flight_vertica\n", "INNER JOIN &&& airports &&&\n", "ON flight_vertica.ORIGIN_AIRPORT = airports.IATA_CODE;" ] }, { "cell_type": "markdown", "id": "931702f0", "metadata": {}, "source": [ "Let's try another query to find the TAIL_NUMBER and AIRLINE of all the flights by joining the two tables: \n", "
    \n", "
  • flight_vertica (stored in Vertica)
    • \n", "
  • airline (stored in PostgreSQL)
    • \n", "
" ] }, { "cell_type": "code", "execution_count": 30, "id": "d2776d21", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
Execution: 0.113s
" ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "
Abc
TAIL_NUMBER
Varchar(20)
Abc
AIRLINE
Varchar(1024)
1N787AAAmerican Airlines Inc.
2N787AAAmerican Airlines Inc.
3N795AAAmerican Airlines Inc.
4N795AAAmerican Airlines Inc.
5N798AAAmerican Airlines Inc.
6N798AAAmerican Airlines Inc.
7N799AAAmerican Airlines Inc.
8N799AAAmerican Airlines Inc.
9N376AAAmerican Airlines Inc.
10N376AAAmerican Airlines Inc.
11N398AAAmerican Airlines Inc.
12N398AAAmerican Airlines Inc.
13N398AAAmerican Airlines Inc.
14N398AAAmerican Airlines Inc.
15N368AAAmerican Airlines Inc.
16N368AAAmerican Airlines Inc.
17N792AAAmerican Airlines Inc.
18N792AAAmerican Airlines Inc.
19N796AAAmerican Airlines Inc.
20N796AAAmerican Airlines Inc.
21N792AAAmerican Airlines Inc.
22N792AAAmerican Airlines Inc.
23N5ERAAAmerican Airlines Inc.
24N5ERAAAmerican Airlines Inc.
25N783AAAmerican Airlines Inc.
26N783AAAmerican Airlines Inc.
27N783AAAmerican Airlines Inc.
28N783AAAmerican Airlines Inc.
29N019AAAmerican Airlines Inc.
30N019AAAmerican Airlines Inc.
31N786AAAmerican Airlines Inc.
32N786AAAmerican Airlines Inc.
33N789AAAmerican Airlines Inc.
34N789AAAmerican Airlines Inc.
35N795AAAmerican Airlines Inc.
36N795AAAmerican Airlines Inc.
37N786AAAmerican Airlines Inc.
38N786AAAmerican Airlines Inc.
39N371AAAmerican Airlines Inc.
40N371AAAmerican Airlines Inc.
41N371AAAmerican Airlines Inc.
42N371AAAmerican Airlines Inc.
43N784AAAmerican Airlines Inc.
44N784AAAmerican Airlines Inc.
45N3AVAAAmerican Airlines Inc.
46N3AVAAAmerican Airlines Inc.
47N5DUAAAmerican Airlines Inc.
48N5DUAAAmerican Airlines Inc.
49N358AAAmerican Airlines Inc.
50N358AAAmerican Airlines Inc.
51N455AAAmerican Airlines Inc.
52N455AAAmerican Airlines Inc.
53N568AAAmerican Airlines Inc.
54N568AAAmerican Airlines Inc.
55N795AAAmerican Airlines Inc.
56N795AAAmerican Airlines Inc.
57N5ESAAAmerican Airlines Inc.
58N5ESAAAmerican Airlines Inc.
59N794AAAmerican Airlines Inc.
60N794AAAmerican Airlines Inc.
61N3ALAAAmerican Airlines Inc.
62N3ALAAAmerican Airlines Inc.
63N3ALAAAmerican Airlines Inc.
64N3ALAAAmerican Airlines Inc.
65N3EKAAAmerican Airlines Inc.
66N3EKAAAmerican Airlines Inc.
67N483AAAmerican Airlines Inc.
68N483AAAmerican Airlines Inc.
69N3BYAAAmerican Airlines Inc.
70N3BYAAAmerican Airlines Inc.
71N3KEAAAmerican Airlines Inc.
72N3KEAAAmerican Airlines Inc.
73N3GPAAAmerican Airlines Inc.
74N3GPAAAmerican Airlines Inc.
75N3BNAAAmerican Airlines Inc.
76N3BNAAAmerican Airlines Inc.
77N3KCAAAmerican Airlines Inc.
78N3KCAAAmerican Airlines Inc.
79N3AJAAAmerican Airlines Inc.
80N3AJAAAmerican Airlines Inc.
81N3ELAAAmerican Airlines Inc.
82N3ELAAAmerican Airlines Inc.
83N3CSAAAmerican Airlines Inc.
84N3CSAAAmerican Airlines Inc.
85N3KSAAAmerican Airlines Inc.
86N3KSAAAmerican Airlines Inc.
87N3JXAAAmerican Airlines Inc.
88N3JXAAAmerican Airlines Inc.
89N5ELAAAmerican Airlines Inc.
90N5ELAAAmerican Airlines Inc.
91N570AAAmerican Airlines Inc.
92N570AAAmerican Airlines Inc.
93N471AAAmerican Airlines Inc.
94N471AAAmerican Airlines Inc.
95N3LMAAAmerican Airlines Inc.
96N3LMAAAmerican Airlines Inc.
97N3LHAAAmerican Airlines Inc.
98N3LHAAAmerican Airlines Inc.
99N3CWAAAmerican Airlines Inc.
100N3CWAAAmerican Airlines Inc.
Columns: 2
" ], "text/plain": [ " TAIL_NUMBER AIRLINE \n", "1 N787AA American Airlines Inc. \n", "2 N787AA American Airlines Inc. \n", "3 N795AA American Airlines Inc. \n", "4 N795AA American Airlines Inc. \n", "5 N798AA American Airlines Inc. \n", "6 N798AA American Airlines Inc. \n", "7 N799AA American Airlines Inc. \n", "8 N799AA American Airlines Inc. \n", "9 N376AA American Airlines Inc. \n", "10 N376AA American Airlines Inc. \n", "11 N398AA American Airlines Inc. \n", "12 N398AA American Airlines Inc. \n", "13 N398AA American Airlines Inc. \n", "14 N398AA American Airlines Inc. \n", "15 N368AA American Airlines Inc. \n", "16 N368AA American Airlines Inc. \n", "17 N792AA American Airlines Inc. \n", "18 N792AA American Airlines Inc. \n", "19 N796AA American Airlines Inc. \n", "20 N796AA American Airlines Inc. \n", "21 N792AA American Airlines Inc. \n", "22 N792AA American Airlines Inc. \n", "23 N5ERAA American Airlines Inc. \n", "24 N5ERAA American Airlines Inc. \n", "25 N783AA American Airlines Inc. \n", "26 N783AA American Airlines Inc. \n", "27 N783AA American Airlines Inc. \n", "28 N783AA American Airlines Inc. \n", "29 N019AA American Airlines Inc. \n", "30 N019AA American Airlines Inc. \n", "31 N786AA American Airlines Inc. \n", "32 N786AA American Airlines Inc. \n", "33 N789AA American Airlines Inc. \n", "34 N789AA American Airlines Inc. \n", "35 N795AA American Airlines Inc. \n", "36 N795AA American Airlines Inc. \n", "37 N786AA American Airlines Inc. \n", "38 N786AA American Airlines Inc. \n", "39 N371AA American Airlines Inc. \n", "40 N371AA American Airlines Inc. \n", "41 N371AA American Airlines Inc. \n", "42 N371AA American Airlines Inc. \n", "43 N784AA American Airlines Inc. \n", "44 N784AA American Airlines Inc. \n", "45 N3AVAA American Airlines Inc. \n", "46 N3AVAA American Airlines Inc. \n", "47 N5DUAA American Airlines Inc. \n", "48 N5DUAA American Airlines Inc. \n", "49 N358AA American Airlines Inc. \n", "50 N358AA American Airlines Inc. \n", "51 N455AA American Airlines Inc. \n", "52 N455AA American Airlines Inc. \n", "53 N568AA American Airlines Inc. \n", "54 N568AA American Airlines Inc. \n", "55 N795AA American Airlines Inc. \n", "56 N795AA American Airlines Inc. \n", "57 N5ESAA American Airlines Inc. \n", "58 N5ESAA American Airlines Inc. \n", "59 N794AA American Airlines Inc. \n", "60 N794AA American Airlines Inc. \n", "61 N3ALAA American Airlines Inc. \n", "62 N3ALAA American Airlines Inc. \n", "63 N3ALAA American Airlines Inc. \n", "64 N3ALAA American Airlines Inc. \n", "65 N3EKAA American Airlines Inc. \n", "66 N3EKAA American Airlines Inc. \n", "67 N483AA American Airlines Inc. \n", "68 N483AA American Airlines Inc. \n", "69 N3BYAA American Airlines Inc. \n", "70 N3BYAA American Airlines Inc. \n", "71 N3KEAA American Airlines Inc. \n", "72 N3KEAA American Airlines Inc. \n", "73 N3GPAA American Airlines Inc. \n", "74 N3GPAA American Airlines Inc. \n", "75 N3BNAA American Airlines Inc. \n", "76 N3BNAA American Airlines Inc. \n", "77 N3KCAA American Airlines Inc. \n", "78 N3KCAA American Airlines Inc. \n", "79 N3AJAA American Airlines Inc. \n", "80 N3AJAA American Airlines Inc. \n", "81 N3ELAA American Airlines Inc. \n", "82 N3ELAA American Airlines Inc. \n", "83 N3CSAA American Airlines Inc. \n", "84 N3CSAA American Airlines Inc. \n", "85 N3KSAA American Airlines Inc. \n", "86 N3KSAA American Airlines Inc. \n", "87 N3JXAA American Airlines Inc. \n", "88 N3JXAA American Airlines Inc. \n", "89 N5ELAA American Airlines Inc. \n", "90 N5ELAA American Airlines Inc. \n", "91 N570AA American Airlines Inc. \n", "92 N570AA American Airlines Inc. \n", "93 N471AA American Airlines Inc. \n", "94 N471AA American Airlines Inc. \n", "95 N3LMAA American Airlines Inc. \n", "96 N3LMAA American Airlines Inc. \n", "97 N3LHAA American Airlines Inc. \n", "98 N3LHAA American Airlines Inc. \n", "99 N3CWAA American Airlines Inc. \n", "100 N3CWAA American Airlines Inc. \n", "Columns: 2" ] }, "execution_count": 30, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "/* Fetch TAIL_NUMBER and AIRLINE after Joining the flight_vertica table with airline table in PostgreSQL database. */\n", "SELECT flight_vertica.TAIL_NUMBER, airline.AIRLINE\n", "FROM flight_vertica\n", "INNER JOIN $$$ airline $$$ \n", "ON flight_vertica.AIRLINE = airline.IATA_CODE;" ] }, { "cell_type": "markdown", "id": "9976e7bf", "metadata": {}, "source": [ "We can even try queries that require multiple joins.\n", "\n", "In the following example, we try to get the TAIL_NUMBER, AIRLINE, and CITY details for all the flights by joining:\n", "
    \n", "
  • flight_local table (stored in Vertica)
    • \n", "
  • airline table (stored in PostgreSQL)
    • \n", "
  • airports table (stored in MySQL)
    • \n", "
" ] }, { "cell_type": "code", "execution_count": 10, "id": "2910f626", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
Execution: 0.095s
" ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "
123
FLIGHT_NUMBER
Integer
Abc
AIRLINE
Varchar(1024)
Abc
STATE
Varchar(2)
11American Airlines Inc.NY
21American Airlines Inc.NY
32American Airlines Inc.CA
42American Airlines Inc.CA
53American Airlines Inc.NY
63American Airlines Inc.NY
74American Airlines Inc.CA
84American Airlines Inc.CA
95American Airlines Inc.TX
105American Airlines Inc.TX
116American Airlines Inc.HI
126American Airlines Inc.HI
137American Airlines Inc.TX
147American Airlines Inc.TX
158American Airlines Inc.HI
168American Airlines Inc.HI
179American Airlines Inc.NY
189American Airlines Inc.NY
1910American Airlines Inc.CA
2010American Airlines Inc.CA
2112American Airlines Inc.CA
2212American Airlines Inc.CA
2314American Airlines Inc.HI
2414American Airlines Inc.HI
2515American Airlines Inc.NY
2615American Airlines Inc.NY
2716American Airlines Inc.CA
2816American Airlines Inc.CA
2917American Airlines Inc.GA
3017American Airlines Inc.GA
3119American Airlines Inc.NY
3219American Airlines Inc.NY
3320American Airlines Inc.CA
3420American Airlines Inc.CA
3521American Airlines Inc.NY
3621American Airlines Inc.NY
3722American Airlines Inc.CA
3822American Airlines Inc.CA
3923American Airlines Inc.TX
4023American Airlines Inc.TX
4123American Airlines Inc.CA
4223American Airlines Inc.CA
4324American Airlines Inc.CA
4424American Airlines Inc.CA
4525American Airlines Inc.MA
4625American Airlines Inc.MA
4727American Airlines Inc.TX
4827American Airlines Inc.TX
4928American Airlines Inc.CA
5028American Airlines Inc.CA
5129American Airlines Inc.CA
5229American Airlines Inc.CA
5329American Airlines Inc.TX
5429American Airlines Inc.TX
5530American Airlines Inc.CA
5630American Airlines Inc.CA
5731American Airlines Inc.CA
5831American Airlines Inc.CA
5932American Airlines Inc.CA
6032American Airlines Inc.CA
6135American Airlines Inc.TX
6235American Airlines Inc.TX
6335American Airlines Inc.CO
6435American Airlines Inc.CO
6536American Airlines Inc.CA
6636American Airlines Inc.CA
6737American Airlines Inc.TX
6837American Airlines Inc.TX
6940American Airlines Inc.VA
7040American Airlines Inc.VA
7141American Airlines Inc.IL
7241American Airlines Inc.IL
7342American Airlines Inc.WA
7442American Airlines Inc.WA
7543American Airlines Inc.TX
7643American Airlines Inc.TX
7743American Airlines Inc.MI
7843American Airlines Inc.MI
7944American Airlines Inc.NV
8044American Airlines Inc.NV
8145American Airlines Inc.NY
8245American Airlines Inc.NY
8346American Airlines Inc.CA
8446American Airlines Inc.CA
8547American Airlines Inc.IL
8647American Airlines Inc.IL
8748American Airlines Inc.CA
8848American Airlines Inc.CA
8949American Airlines Inc.TX
9049American Airlines Inc.TX
9150American Airlines Inc.UT
9250American Airlines Inc.UT
9351American Airlines Inc.TX
9451American Airlines Inc.TX
9552American Airlines Inc.CA
9652American Airlines Inc.CA
9753American Airlines Inc.VA
9853American Airlines Inc.VA
9954American Airlines Inc.NV
10054American Airlines Inc.NV
Columns: 3
" ], "text/plain": [ " FLIGHT_NUMBER AIRLINE STATE \n", "1 1 American Airlines Inc. NY \n", "2 1 American Airlines Inc. NY \n", "3 2 American Airlines Inc. CA \n", "4 2 American Airlines Inc. CA \n", "5 3 American Airlines Inc. NY \n", "6 3 American Airlines Inc. NY \n", "7 4 American Airlines Inc. CA \n", "8 4 American Airlines Inc. CA \n", "9 5 American Airlines Inc. TX \n", "10 5 American Airlines Inc. TX \n", "11 6 American Airlines Inc. HI \n", "12 6 American Airlines Inc. HI \n", "13 7 American Airlines Inc. TX \n", "14 7 American Airlines Inc. TX \n", "15 8 American Airlines Inc. HI \n", "16 8 American Airlines Inc. HI \n", "17 9 American Airlines Inc. NY \n", "18 9 American Airlines Inc. NY \n", "19 10 American Airlines Inc. CA \n", "20 10 American Airlines Inc. CA \n", "21 12 American Airlines Inc. CA \n", "22 12 American Airlines Inc. CA \n", "23 14 American Airlines Inc. HI \n", "24 14 American Airlines Inc. HI \n", "25 15 American Airlines Inc. NY \n", "26 15 American Airlines Inc. NY \n", "27 16 American Airlines Inc. CA \n", "28 16 American Airlines Inc. CA \n", "29 17 American Airlines Inc. GA \n", "30 17 American Airlines Inc. GA \n", "31 19 American Airlines Inc. NY \n", "32 19 American Airlines Inc. NY \n", "33 20 American Airlines Inc. CA \n", "34 20 American Airlines Inc. CA \n", "35 21 American Airlines Inc. NY \n", "36 21 American Airlines Inc. NY \n", "37 22 American Airlines Inc. CA \n", "38 22 American Airlines Inc. CA \n", "39 23 American Airlines Inc. TX \n", "40 23 American Airlines Inc. TX \n", "41 23 American Airlines Inc. CA \n", "42 23 American Airlines Inc. CA \n", "43 24 American Airlines Inc. CA \n", "44 24 American Airlines Inc. CA \n", "45 25 American Airlines Inc. MA \n", "46 25 American Airlines Inc. MA \n", "47 27 American Airlines Inc. TX \n", "48 27 American Airlines Inc. TX \n", "49 28 American Airlines Inc. CA \n", "50 28 American Airlines Inc. CA \n", "51 29 American Airlines Inc. CA \n", "52 29 American Airlines Inc. CA \n", "53 29 American Airlines Inc. TX \n", "54 29 American Airlines Inc. TX \n", "55 30 American Airlines Inc. CA \n", "56 30 American Airlines Inc. CA \n", "57 31 American Airlines Inc. CA \n", "58 31 American Airlines Inc. CA \n", "59 32 American Airlines Inc. CA \n", "60 32 American Airlines Inc. CA \n", "61 35 American Airlines Inc. TX \n", "62 35 American Airlines Inc. TX \n", "63 35 American Airlines Inc. CO \n", "64 35 American Airlines Inc. CO \n", "65 36 American Airlines Inc. CA \n", "66 36 American Airlines Inc. CA \n", "67 37 American Airlines Inc. TX \n", "68 37 American Airlines Inc. TX \n", "69 40 American Airlines Inc. VA \n", "70 40 American Airlines Inc. VA \n", "71 41 American Airlines Inc. IL \n", "72 41 American Airlines Inc. IL \n", "73 42 American Airlines Inc. WA \n", "74 42 American Airlines Inc. WA \n", "75 43 American Airlines Inc. TX \n", "76 43 American Airlines Inc. TX \n", "77 43 American Airlines Inc. MI \n", "78 43 American Airlines Inc. MI \n", "79 44 American Airlines Inc. NV \n", "80 44 American Airlines Inc. NV \n", "81 45 American Airlines Inc. NY \n", "82 45 American Airlines Inc. NY \n", "83 46 American Airlines Inc. CA \n", "84 46 American Airlines Inc. CA \n", "85 47 American Airlines Inc. IL \n", "86 47 American Airlines Inc. IL \n", "87 48 American Airlines Inc. CA \n", "88 48 American Airlines Inc. CA \n", "89 49 American Airlines Inc. TX \n", "90 49 American Airlines Inc. TX \n", "91 50 American Airlines Inc. UT \n", "92 50 American Airlines Inc. UT \n", "93 51 American Airlines Inc. TX \n", "94 51 American Airlines Inc. TX \n", "95 52 American Airlines Inc. CA \n", "96 52 American Airlines Inc. CA \n", "97 53 American Airlines Inc. VA \n", "98 53 American Airlines Inc. VA \n", "99 54 American Airlines Inc. NV \n", "100 54 American Airlines Inc. NV \n", "Columns: 3" ] }, "execution_count": 10, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "/* Fetch FLIGHT_NUMBER, AIRLINE and STATE after Joining the flight_vertica table with two other tables from different databases. */\n", "SELECT flight_vertica.FLIGHT_NUMBER, airline.AIRLINE, airports.STATE\n", "FROM flight_vertica\n", "INNER JOIN $$$ airline $$$ \n", "ON flight_vertica.AIRLINE = airline.IATA_CODE\n", "INNER JOIN &&& airports &&&\n", "ON flight_vertica.ORIGIN_AIRPORT = airports.IATA_CODE;" ] }, { "cell_type": "markdown", "id": "dc25f083", "metadata": {}, "source": [ "### Pandas.DataFrame" ] }, { "cell_type": "markdown", "id": "7e2d0906", "metadata": {}, "source": [ "The joins also work with pandas.Dataframe. We can perform the same query that required multiple joins, but now with a local Pandas dataframe.\n", "\n", "We will first read the local CSV file." ] }, { "cell_type": "code", "execution_count": 35, "id": "1c651b16", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
FLIGHT_NUMBERPASSENGER_COUNT
098293
12336222
2840188
3258143
4135267
.........
54014106266
54024113157
5403979226
54045430251
54054157228
\n", "

5406 rows × 2 columns

\n", "
" ], "text/plain": [ " FLIGHT_NUMBER PASSENGER_COUNT\n", "0 98 293\n", "1 2336 222\n", "2 840 188\n", "3 258 143\n", "4 135 267\n", "... ... ...\n", "5401 4106 266\n", "5402 4113 157\n", "5403 979 226\n", "5404 5430 251\n", "5405 4157 228\n", "\n", "[5406 rows x 2 columns]" ] }, "execution_count": 35, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Create a Pandas Data Frame after importing the csv file \"passengers.csv\"\n", "import pandas as pd\n", "passengers_pandas = pd.read_csv('passengers.csv')\n", "passengers_pandas" ] }, { "cell_type": "markdown", "id": "73d6a005", "metadata": {}, "source": [ "We can now perform the same query involving the three tables:\n", "
    \n", "
  • flight_vertica table (stored in Vertica)
    • \n", "
  • passengers_pandas table (pandas.DataFrame stored in-memory)
    • \n", "
  • airline table (stored in PostgreSQL)
    • \n", "
  • airports table (stored in MySQL)
    • \n", "
" ] }, { "cell_type": "code", "execution_count": 33, "id": "3f820c99", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
Execution: 0.097s
" ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "
Abc
TAIL_NUMBER
Varchar(20)
Abc
AIRLINE
Varchar(1024)
Abc
CITY
Varchar(60)
123
PASSENGER_COUNT
Integer
1N787AAAmerican Airlines Inc.New York252
2N787AAAmerican Airlines Inc.New York252
3N795AAAmerican Airlines Inc.Los Angeles193
4N795AAAmerican Airlines Inc.Los Angeles193
5N798AAAmerican Airlines Inc.New York201
6N798AAAmerican Airlines Inc.New York201
7N799AAAmerican Airlines Inc.Los Angeles210
8N799AAAmerican Airlines Inc.Los Angeles210
9N376AAAmerican Airlines Inc.Dallas-Fort Worth190
10N376AAAmerican Airlines Inc.Dallas-Fort Worth190
11N398AAAmerican Airlines Inc.Kahului181
12N398AAAmerican Airlines Inc.Kahului181
13N398AAAmerican Airlines Inc.Dallas-Fort Worth195
14N398AAAmerican Airlines Inc.Dallas-Fort Worth195
15N368AAAmerican Airlines Inc.Honolulu117
16N368AAAmerican Airlines Inc.Honolulu117
17N792AAAmerican Airlines Inc.New York221
18N792AAAmerican Airlines Inc.New York221
19N796AAAmerican Airlines Inc.Los Angeles245
20N796AAAmerican Airlines Inc.Los Angeles245
21N792AAAmerican Airlines Inc.Los Angeles280
22N792AAAmerican Airlines Inc.Los Angeles280
23N5ERAAAmerican Airlines Inc.Kahului259
24N5ERAAAmerican Airlines Inc.Kahului259
25N783AAAmerican Airlines Inc.New York184
26N783AAAmerican Airlines Inc.New York184
27N783AAAmerican Airlines Inc.San Francisco173
28N783AAAmerican Airlines Inc.San Francisco173
29N019AAAmerican Airlines Inc.Atlanta244
30N019AAAmerican Airlines Inc.Atlanta244
31N786AAAmerican Airlines Inc.New York235
32N786AAAmerican Airlines Inc.New York235
33N789AAAmerican Airlines Inc.San Francisco156
34N789AAAmerican Airlines Inc.San Francisco156
35N795AAAmerican Airlines Inc.New York204
36N795AAAmerican Airlines Inc.New York204
37N786AAAmerican Airlines Inc.Los Angeles210
38N786AAAmerican Airlines Inc.Los Angeles210
39N371AAAmerican Airlines Inc.Dallas-Fort Worth131
40N371AAAmerican Airlines Inc.Dallas-Fort Worth131
41N371AAAmerican Airlines Inc.San Francisco131
42N371AAAmerican Airlines Inc.San Francisco131
43N784AAAmerican Airlines Inc.San Francisco152
44N784AAAmerican Airlines Inc.San Francisco152
45N3AVAAAmerican Airlines Inc.Boston155
46N3AVAAAmerican Airlines Inc.Boston155
47N5DUAAAmerican Airlines Inc.Dallas-Fort Worth210
48N5DUAAAmerican Airlines Inc.Dallas-Fort Worth210
49N358AAAmerican Airlines Inc.Los Angeles224
50N358AAAmerican Airlines Inc.Los Angeles224
51N455AAAmerican Airlines Inc.Ontario141
52N455AAAmerican Airlines Inc.Ontario141
53N568AAAmerican Airlines Inc.Dallas-Fort Worth141
54N568AAAmerican Airlines Inc.Dallas-Fort Worth141
55N795AAAmerican Airlines Inc.Los Angeles238
56N795AAAmerican Airlines Inc.Los Angeles238
57N5ESAAAmerican Airlines Inc.Los Angeles148
58N5ESAAAmerican Airlines Inc.Los Angeles148
59N794AAAmerican Airlines Inc.Los Angeles200
60N794AAAmerican Airlines Inc.Los Angeles200
61N3ALAAAmerican Airlines Inc.Dallas-Fort Worth138
62N3ALAAAmerican Airlines Inc.Dallas-Fort Worth138
63N3ALAAAmerican Airlines Inc.Hayden138
64N3ALAAAmerican Airlines Inc.Hayden138
65N3EKAAAmerican Airlines Inc.San Diego258
66N3EKAAAmerican Airlines Inc.San Diego258
67N483AAAmerican Airlines Inc.Dallas-Fort Worth272
68N483AAAmerican Airlines Inc.Dallas-Fort Worth272
69N3BYAAAmerican Airlines Inc.Arlington189
70N3BYAAAmerican Airlines Inc.Arlington189
71N3KEAAAmerican Airlines Inc.Chicago161
72N3KEAAAmerican Airlines Inc.Chicago161
73N3GPAAAmerican Airlines Inc.Seattle186
74N3GPAAAmerican Airlines Inc.Seattle186
75N3BNAAAmerican Airlines Inc.Dallas-Fort Worth209
76N3BNAAAmerican Airlines Inc.Dallas-Fort Worth209
77N3KCAAAmerican Airlines Inc.Detroit209
78N3KCAAAmerican Airlines Inc.Detroit209
79N3AJAAAmerican Airlines Inc.Las Vegas118
80N3AJAAAmerican Airlines Inc.Las Vegas118
81N3ELAAAmerican Airlines Inc.New York208
82N3ELAAAmerican Airlines Inc.New York208
83N3CSAAAmerican Airlines Inc.San Jose266
84N3CSAAAmerican Airlines Inc.San Jose266
85N3KSAAAmerican Airlines Inc.Chicago278
86N3KSAAAmerican Airlines Inc.Chicago278
87N3JXAAAmerican Airlines Inc.San Diego213
88N3JXAAAmerican Airlines Inc.San Diego213
89N5ELAAAmerican Airlines Inc.Dallas-Fort Worth207
90N5ELAAAmerican Airlines Inc.Dallas-Fort Worth207
91N570AAAmerican Airlines Inc.Salt Lake City176
92N570AAAmerican Airlines Inc.Salt Lake City176
93N471AAAmerican Airlines Inc.Dallas-Fort Worth234
94N471AAAmerican Airlines Inc.Dallas-Fort Worth234
95N3LMAAAmerican Airlines Inc.Los Angeles149
96N3LMAAAmerican Airlines Inc.Los Angeles149
97N3LHAAAmerican Airlines Inc.Arlington277
98N3LHAAAmerican Airlines Inc.Arlington277
99N3CWAAAmerican Airlines Inc.Las Vegas193
100N3CWAAAmerican Airlines Inc.Las Vegas193
Columns: 4
" ], "text/plain": [ " TAIL_NUMBER AIRLINE CITY \\\\\n", "1 N787AA American Airlines Inc. New York \\\\\n", "2 N787AA American Airlines Inc. New York \\\\\n", "3 N795AA American Airlines Inc. Los Angeles \\\\\n", "4 N795AA American Airlines Inc. Los Angeles \\\\\n", "5 N798AA American Airlines Inc. New York \\\\\n", "6 N798AA American Airlines Inc. New York \\\\\n", "7 N799AA American Airlines Inc. Los Angeles \\\\\n", "8 N799AA American Airlines Inc. Los Angeles \\\\\n", "9 N376AA American Airlines Inc. Dallas-Fort Worth \\\\\n", "10 N376AA American Airlines Inc. Dallas-Fort Worth \\\\\n", "11 N398AA American Airlines Inc. Kahului \\\\\n", "12 N398AA American Airlines Inc. Kahului \\\\\n", "13 N398AA American Airlines Inc. Dallas-Fort Worth \\\\\n", "14 N398AA American Airlines Inc. Dallas-Fort Worth \\\\\n", "15 N368AA American Airlines Inc. Honolulu \\\\\n", "16 N368AA American Airlines Inc. Honolulu \\\\\n", "17 N792AA American Airlines Inc. New York \\\\\n", "18 N792AA American Airlines Inc. New York \\\\\n", "19 N796AA American Airlines Inc. Los Angeles \\\\\n", "20 N796AA American Airlines Inc. Los Angeles \\\\\n", "21 N792AA American Airlines Inc. Los Angeles \\\\\n", "22 N792AA American Airlines Inc. Los Angeles \\\\\n", "23 N5ERAA American Airlines Inc. Kahului \\\\\n", "24 N5ERAA American Airlines Inc. Kahului \\\\\n", "25 N783AA American Airlines Inc. New York \\\\\n", "26 N783AA American Airlines Inc. New York \\\\\n", "27 N783AA American Airlines Inc. San Francisco \\\\\n", "28 N783AA American Airlines Inc. San Francisco \\\\\n", "29 N019AA American Airlines Inc. Atlanta \\\\\n", "30 N019AA American Airlines Inc. Atlanta \\\\\n", "31 N786AA American Airlines Inc. New York \\\\\n", "32 N786AA American Airlines Inc. New York \\\\\n", "33 N789AA American Airlines Inc. San Francisco \\\\\n", "34 N789AA American Airlines Inc. San Francisco \\\\\n", "35 N795AA American Airlines Inc. New York \\\\\n", "36 N795AA American Airlines Inc. New York \\\\\n", "37 N786AA American Airlines Inc. Los Angeles \\\\\n", "38 N786AA American Airlines Inc. Los Angeles \\\\\n", "39 N371AA American Airlines Inc. Dallas-Fort Worth \\\\\n", "40 N371AA American Airlines Inc. Dallas-Fort Worth \\\\\n", "41 N371AA American Airlines Inc. San Francisco \\\\\n", "42 N371AA American Airlines Inc. San Francisco \\\\\n", "43 N784AA American Airlines Inc. San Francisco \\\\\n", "44 N784AA American Airlines Inc. San Francisco \\\\\n", "45 N3AVAA American Airlines Inc. Boston \\\\\n", "46 N3AVAA American Airlines Inc. Boston \\\\\n", "47 N5DUAA American Airlines Inc. Dallas-Fort Worth \\\\\n", "48 N5DUAA American Airlines Inc. Dallas-Fort Worth \\\\\n", "49 N358AA American Airlines Inc. Los Angeles \\\\\n", "50 N358AA American Airlines Inc. Los Angeles \\\\\n", "51 N455AA American Airlines Inc. Ontario \\\\\n", "52 N455AA American Airlines Inc. Ontario \\\\\n", "53 N568AA American Airlines Inc. Dallas-Fort Worth \\\\\n", "54 N568AA American Airlines Inc. Dallas-Fort Worth \\\\\n", "55 N795AA American Airlines Inc. Los Angeles \\\\\n", "56 N795AA American Airlines Inc. Los Angeles \\\\\n", "57 N5ESAA American Airlines Inc. Los Angeles \\\\\n", "58 N5ESAA American Airlines Inc. Los Angeles \\\\\n", "59 N794AA American Airlines Inc. Los Angeles \\\\\n", "60 N794AA American Airlines Inc. Los Angeles \\\\\n", "61 N3ALAA American Airlines Inc. Dallas-Fort Worth \\\\\n", "62 N3ALAA American Airlines Inc. Dallas-Fort Worth \\\\\n", "63 N3ALAA American Airlines Inc. Hayden \\\\\n", "64 N3ALAA American Airlines Inc. Hayden \\\\\n", "65 N3EKAA American Airlines Inc. San Diego \\\\\n", "66 N3EKAA American Airlines Inc. San Diego \\\\\n", "67 N483AA American Airlines Inc. Dallas-Fort Worth \\\\\n", "68 N483AA American Airlines Inc. Dallas-Fort Worth \\\\\n", "69 N3BYAA American Airlines Inc. Arlington \\\\\n", "70 N3BYAA American Airlines Inc. Arlington \\\\\n", "71 N3KEAA American Airlines Inc. Chicago \\\\\n", "72 N3KEAA American Airlines Inc. Chicago \\\\\n", "73 N3GPAA American Airlines Inc. Seattle \\\\\n", "74 N3GPAA American Airlines Inc. Seattle \\\\\n", "75 N3BNAA American Airlines Inc. Dallas-Fort Worth \\\\\n", "76 N3BNAA American Airlines Inc. Dallas-Fort Worth \\\\\n", "77 N3KCAA American Airlines Inc. Detroit \\\\\n", "78 N3KCAA American Airlines Inc. Detroit \\\\\n", "79 N3AJAA American Airlines Inc. Las Vegas \\\\\n", "80 N3AJAA American Airlines Inc. Las Vegas \\\\\n", "81 N3ELAA American Airlines Inc. New York \\\\\n", "82 N3ELAA American Airlines Inc. New York \\\\\n", "83 N3CSAA American Airlines Inc. San Jose \\\\\n", "84 N3CSAA American Airlines Inc. San Jose \\\\\n", "85 N3KSAA American Airlines Inc. Chicago \\\\\n", "86 N3KSAA American Airlines Inc. Chicago \\\\\n", "87 N3JXAA American Airlines Inc. San Diego \\\\\n", "88 N3JXAA American Airlines Inc. San Diego \\\\\n", "89 N5ELAA American Airlines Inc. Dallas-Fort Worth \\\\\n", "90 N5ELAA American Airlines Inc. Dallas-Fort Worth \\\\\n", "91 N570AA American Airlines Inc. Salt Lake City \\\\\n", "92 N570AA American Airlines Inc. Salt Lake City \\\\\n", "93 N471AA American Airlines Inc. Dallas-Fort Worth \\\\\n", "94 N471AA American Airlines Inc. Dallas-Fort Worth \\\\\n", "95 N3LMAA American Airlines Inc. Los Angeles \\\\\n", "96 N3LMAA American Airlines Inc. Los Angeles \\\\\n", "97 N3LHAA American Airlines Inc. Arlington \\\\\n", "98 N3LHAA American Airlines Inc. Arlington \\\\\n", "99 N3CWAA American Airlines Inc. Las Vegas \\\\\n", "100 N3CWAA American Airlines Inc. Las Vegas \\\\\n", " PASSENGER_COUNT \n", "1 252 \n", "2 252 \n", "3 193 \n", "4 193 \n", "5 201 \n", "6 201 \n", "7 210 \n", "8 210 \n", "9 190 \n", "10 190 \n", "11 181 \n", "12 181 \n", "13 195 \n", "14 195 \n", "15 117 \n", "16 117 \n", "17 221 \n", "18 221 \n", "19 245 \n", "20 245 \n", "21 280 \n", "22 280 \n", "23 259 \n", "24 259 \n", "25 184 \n", "26 184 \n", "27 173 \n", "28 173 \n", "29 244 \n", "30 244 \n", "31 235 \n", "32 235 \n", "33 156 \n", "34 156 \n", "35 204 \n", "36 204 \n", "37 210 \n", "38 210 \n", "39 131 \n", "40 131 \n", "41 131 \n", "42 131 \n", "43 152 \n", "44 152 \n", "45 155 \n", "46 155 \n", "47 210 \n", "48 210 \n", "49 224 \n", "50 224 \n", "51 141 \n", "52 141 \n", "53 141 \n", "54 141 \n", "55 238 \n", "56 238 \n", "57 148 \n", "58 148 \n", "59 200 \n", "60 200 \n", "61 138 \n", "62 138 \n", "63 138 \n", "64 138 \n", "65 258 \n", "66 258 \n", "67 272 \n", "68 272 \n", "69 189 \n", "70 189 \n", "71 161 \n", "72 161 \n", "73 186 \n", "74 186 \n", "75 209 \n", "76 209 \n", "77 209 \n", "78 209 \n", "79 118 \n", "80 118 \n", "81 208 \n", "82 208 \n", "83 266 \n", "84 266 \n", "85 278 \n", "86 278 \n", "87 213 \n", "88 213 \n", "89 207 \n", "90 207 \n", "91 176 \n", "92 176 \n", "93 234 \n", "94 234 \n", "95 149 \n", "96 149 \n", "97 277 \n", "98 277 \n", "99 193 \n", "100 193 \n", "Columns: 4" ] }, "execution_count": 33, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT \n", " flight_vertica.TAIL_NUMBER, \n", " airline.AIRLINE, \n", " airports.CITY, \n", " :passengers_pandas.PASSENGER_COUNT\n", "FROM flight_vertica\n", "INNER JOIN $$$ airline $$$ \n", "ON flight_vertica.AIRLINE = airline.IATA_CODE\n", "INNER JOIN &&& airports &&&\n", "ON flight_vertica.ORIGIN_AIRPORT = airports.IATA_CODE\n", "INNER JOIN :passengers_pandas\n", "ON flight_vertica.FLIGHT_NUMBER = :passengers_pandas.FLIGHT_NUMBER;" ] }, { "cell_type": "markdown", "id": "7c531882", "metadata": {}, "source": [ "## Conclusion" ] }, { "cell_type": "markdown", "id": "0230f413", "metadata": {}, "source": [ "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.\n", "\n", "The cherry on the cake is the ease-of-use that is enabled by VerticaPy and its Python-like syntax. \n", "\n", "Queries that required paragraph upon paragraph to execute can now be done efficiently with only a few intuitive lines of code.\n", "\n", "This new functionality opens up many possibilities for data querying and manipulation in Vertica." ] } ], "metadata": { "kernelspec": { "display_name": "Python 3 (ipykernel)", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.10.12" } }, "nbformat": 4, "nbformat_minor": 5 }