{"id":14172,"date":"2020-06-04T21:09:27","date_gmt":"2020-06-04T19:09:27","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/oracle-select-from-file\/"},"modified":"2020-06-04T21:09:27","modified_gmt":"2020-06-04T19:09:27","slug":"oracle-select-from-file","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/oracle-select-from-file\/","title":{"rendered":"Oracle 18c – select from a flat file"},"content":{"rendered":"

By Franck Pachot<\/h2>\n

.
\nThis post is the first one from a series of small examples on recent Oracle features. My goal is to present them to people outside of Oracle and relational databases usage, maybe some NoSQL players. And this is why the title is “select from a flat-file” rather than “Inline External Tables”. In my opinion, the names of the features of Oracle Database are invented by the architects and developers, sometimes renamed by Marketing or CTO, and all that is very far from what the users are looking for. In order to understand “Inline External Table” you need to know all the history behind: there were tables, then external tables, and there were queries, and inlined queries, and… But imagine a junior who just wants to query a file, he will never find this feature. He has a file, it is not a table, it is not external, and it is not inline. What is external to him is this SQL language and what we want to show him is that this language can query his file.<\/p>\n

I’m running this in the Oracle 20c preview in the Oracle Cloud.<\/p>\n

In this post, my goal is to load a small fact and dimension table for the next posts about some recent features that are interesting in data warehouses. It is the occasion to show that with Oracle we can easily select from a .csv file, without the need to run SQL*Loader or create an external table.
\nI’m running everything from SQLcl and then I use the host command to call curl:<\/p>\n

\nhost curl -L http:\/\/opendata.ecdc.europa.eu\/covid19\/casedistribution\/csv\/ | dos2unix | (sed -u 1q; sort -t, -n -k4,4 -k3,3 -k2,2 ) > \/tmp\/covid-19.csv\n<\/code><\/pre>\n
This gets the latest number of COVID-19 cases per day and per country.<\/p>\n
It looks like this:<\/p>\n
\nSQL> host head  \/tmp\/covid-19.csv\n\ndateRep,day,month,year,cases,deaths,countriesAndTerritories,geoId,countryterritoryCode,popData2018,continentExp\n31\/12\/2019,31,12,2019,0,0,Afghanistan,AF,AFG,37172386,Asia\n31\/12\/2019,31,12,2019,0,0,Algeria,DZ,DZA,42228429,Africa\n31\/12\/2019,31,12,2019,0,0,Armenia,AM,ARM,2951776,Europe\n31\/12\/2019,31,12,2019,0,0,Australia,AU,AUS,24992369,Oceania\n31\/12\/2019,31,12,2019,0,0,Austria,AT,AUT,8847037,Europe\n31\/12\/2019,31,12,2019,0,0,Azerbaijan,AZ,AZE,9942334,Europe\n31\/12\/2019,31,12,2019,0,0,Bahrain,BH,BHR,1569439,Asia\n31\/12\/2019,31,12,2019,0,0,Belarus,BY,BLR,9485386,Europe\n31\/12\/2019,31,12,2019,0,0,Belgium,BE,BEL,11422068,Europe\n<\/code><\/pre>\n
I sorted them on date on purpose (next post<\/a> may talk about data clustering) but this is the way the file comes anyway.<\/p>\n
I need a directory object to access the file:<\/p>\n
\nSQL> create or replace directory \"\/tmp\" as '\/tmp';\n\nDirectory created.\n<\/code><\/pre>\n
You don’t have to use quoted identifiers if you don’t like it. I find it convenient here.<\/p>\n
I can directly select from the file, the EXTERNAL clause mentioning what we had to put in an external table before 18c:<\/p>\n
\nSQL> \nselect *\n from external (\n  (\n   dateRep                    date\n   ,n_day                     number\n   ,n_month                   number\n   ,n_year                    number\n   ,cases                     number\n   ,deaths                    number\n   ,countriesAndTerritories   varchar2(50)\n   ,geoId                     varchar2(10)\n   ,countryterritoryCode      varchar2(3)\n   ,popData2018               number\n   ,continentExp              varchar2(10)\n  )\n  default directory \"\/tmp\"\n  access parameters (\n     records delimited by newline skip 1 -- skip header\n     logfile 'covid-19.log'\n     badfile 'covid-19.bad'\n     fields terminated by \",\" optionally enclosed by '\"'\n(dateRep date 'dd\/mm\/yyyy',n_day,n_month,n_year,cases,deaths,countriesAndTerritories,geoId,countryterritoryCode,popData2018,continentExp))\n  location ('covid.csv')\n  reject limit 10000 - because I've seen some bad data ;)\n )\n.\n\n\nSQL> \/\n\n      DATEREP    N_DAY    N_MONTH    N_YEAR    CASES    DEATHS                       COUNTRIESANDTERRITORIES       GEOID    COUNTRYTERRITORYCODE    POPDATA2018    CONTINENTEXP\n_____________ ________ __________ _________ ________ _________ _____________________________________________ ___________ _______________________ ______________ _______________\n31\/12\/2019          31         12      2019        0         0 Afghanistan                                   AF          AFG                           37172386 Asia\n31\/12\/2019          31         12      2019        0         0 Algeria                                       DZ          DZA                           42228429 Africa\n31\/12\/2019          31         12      2019        0         0 Armenia                                       AM          ARM                            2951776 Europe\n31\/12\/2019          31         12      2019        0         0 Australia                                     AU          AUS                           24992369 Oceania\n31\/12\/2019          31         12      2019        0         0 Austria                                       AT          AUT                            8847037 Europe\n31\/12\/2019          31         12      2019        0         0 Azerbaijan                                    AZ          AZE                            9942334 Europe\n31\/12\/2019          31         12      2019        0         0 Bahrain                                       BH          BHR                            1569439 Asia\n\nORA-01013: user requested cancel of current operation\n\nSQL>\n\n<\/code><\/pre>\n
I cancelled it as that’s too long to display here.<\/p>\n
As the query is still in the buffer, I just add a CREATE TABLE in front of it:<\/p>\n
\nSQL> 1\n  1* select *\nSQL> c\/select\/create table covid as select\/\n   create table covid as select *\n  2   from external (\n  3    (\n  4     dateRep                    varchar2(10)\n  5     ,day                       number\n...\n\nSQL> \/\n\nTable created.\n\nSQL>\n\n<\/code><\/pre>\n
While I’m there I’ll quickly create a fact table and a dimension hierarchy:<\/p>\n
\nSQL> \ncreate table continents as select rownum continent_id, continentexp continent_name from (select distinct continentexp from covid where continentexp!='Other');\n\nTable created.\n\nSQL> create table countries as select country_id,country_code,country_name,continent_id,popdata2018 from (select distinct geoid country_id,countryterritorycode country_code,countriesandterritories country_name,continentexp continent_name,popdata2018 from covid where continentexp!='Other') left join continents using(continent_name);\n\nTable created.\n\nSQL> \ncreate table cases as select daterep, geoid country_id,cases from covid where continentexp!='Other';\n\nTable created.\n\nSQL> \nalter table continents add primary key (continent_id);\n\nTable altered.\n\nSQL> \nalter table countries add foreign key (continent_id) references continents;\n\nTable altered.\n\nSQL> \nalter table countries add primary key (country_id);\n\nTable altered.\n\nSQL> \nalter table cases add foreign key (country_id) references countries;\n\nTable altered.\n\nSQL> \nalter table cases add primary key (country_id,daterep);\n\nTable altered.\n\nSQL>\n\n<\/code><\/pre>\n
This creates a CASES fact table with only one measure (covid-19 cases) and two dimensions. To get it simple, the date dimension here is just a date column (you usually have a foreign key to a calendar dimension). The geographical dimension is a foreign key to the COUNTRIES table which itself has a foreign key referencing the CONTINENTS table.<\/p>\n
12c Top-N queries<\/h3>\n
In 12c we have a nice syntax for Top-N queries with the FETCH FIRST clause of the ORDER BY:<\/p>\n
\nSQL> \nselect continent_name,country_code,max(cases) from cases join countries using(country_id) join continents using(continent_id) group by continent_name,country_code order by max(cases) desc fetch first 10 rows only;\n\n   CONTINENT_NAME    COUNTRY_CODE    MAX(CASES)\n_________________ _______________ _____________\nAmerica           USA                     48529\nAmerica           BRA                     33274\nAsia              CHN                     15141\nEurope            RUS                     11656\nAmerica           ECU                     11536\nAsia              IND                      9851\nEurope            ESP                      9181\nAmerica           PER                      8875\nEurope            GBR                      8719\nEurope            FRA                      7578\n\n10 rows selected.\n\n<\/code><\/pre>\n
This returns the 10 countries which had the maximum covid-19 cases per day.<\/p>\n
20c WINDOW clauses<\/h3>\n
If I want to show the date with the maximum value, I can use analytic functions and in 20c I don’t have to repeat the window several times:<\/p>\n
\nSQL> \nselect continent_name,country_code,top_date,top_cases from (\n   select continent_name,country_code,daterep,cases\n    ,first_value(daterep)over(w) top_date\n    ,first_value(cases)over(w) top_cases\n    ,row_number()over(w) r\n    from cases join countries using(country_id) join continents using(continent_id)\n    window w as (partition by continent_id order by cases desc)\n   )\n   where r=1 -- this to get the rows with the highest value only\n   order by top_cases desc fetch first 10 rows only;\n\n   CONTINENT_NAME    COUNTRY_CODE      TOP_DATE    TOP_CASES\n_________________ _______________ _____________ ____________\nAmerica           USA             26\/04\/2020           48529\nAsia              CHN             13\/02\/2020           15141\nEurope            RUS             12\/05\/2020           11656\nAfrica            ZAF             05\/06\/2020            3267\nOceania           AUS             23\/03\/2020             611\n\n<\/code><\/pre>\n
The same can be done before 20c but you have to write the (partition by continent_id order by cases desc) for each projection.<\/p>\n
In the next post<\/a> I’ll show a very nice feature. Keeping the 3 tables normalized data model but, because storage is cheap, materializing some pre-computed joins. If you are a fan of NoSQL because “storage is cheap” and “joins are expensive”, then you will see what we can do with SQL in this area…<\/p>\n","protected":false},"excerpt":{"rendered":"
By Franck Pachot . This post is the first one from a series of small examples on recent Oracle features. My goal is to present them to people outside of Oracle and relational databases usage, maybe some NoSQL players. And this is why the title is “select from a flat-file” rather than “Inline External Tables”. […]<\/p>\n","protected":false},"author":27,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[955,229,59],"tags":[62,496,1489,1948,1949,1950,96,1184,458,1951],"type_dbi":[],"class_list":["post-14172","post","type-post","status-publish","format-standard","hentry","category-cloud","category-database-administration-monitoring","category-oracle","tag-19c","tag-bi","tag-dwh","tag-external","tag-features","tag-inline","tag-oracle","tag-oracle-18c","tag-oracle-20c","tag-table"],"acf":[],"yoast_head":"\nOracle 18c - select from a flat file - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/oracle-select-from-file\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Oracle 18c - select from a flat file\" \/>\n<meta property=\"og:description\" content=\"By Franck Pachot . This post is the first one from a series of small examples on recent Oracle features. My goal is to present them to people outside of Oracle and relational databases usage, maybe some NoSQL players. And this is why the title is “select from a flat-file” rather than “Inline External Tables”. […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/oracle-select-from-file\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2020-06-04T19:09:27+00:00\" \/>\n<meta name=\"author\" content=\"Oracle Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Oracle Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"6 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-select-from-file\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-select-from-file\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"Oracle 18c – select from a flat file\",\"datePublished\":\"2020-06-04T19:09:27+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-select-from-file\/\"},\"wordCount\":610,\"commentCount\":0,\"keywords\":[\"19c\",\"BI\",\"DWH\",\"external\",\"features\",\"inline\",\"Oracle\",\"Oracle 18c\",\"Oracle 20c\",\"table\"],\"articleSection\":[\"Cloud\",\"Database Administration & Monitoring\",\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/oracle-select-from-file\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-select-from-file\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/oracle-select-from-file\/\",\"name\":\"Oracle 18c - select from a flat file - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2020-06-04T19:09:27+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-select-from-file\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/oracle-select-from-file\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-select-from-file\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Oracle 18c – select from a flat file\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\",\"name\":\"Oracle Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"caption\":\"Oracle Team\"},\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Oracle 18c - select from a flat file - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/oracle-select-from-file\/","og_locale":"en_US","og_type":"article","og_title":"Oracle 18c - select from a flat file","og_description":"By Franck Pachot . This post is the first one from a series of small examples on recent Oracle features. My goal is to present them to people outside of Oracle and relational databases usage, maybe some NoSQL players. And this is why the title is “select from a flat-file” rather than “Inline External Tables”. […]","og_url":"https:\/\/www.dbi-services.com\/blog\/oracle-select-from-file\/","og_site_name":"dbi Blog","article_published_time":"2020-06-04T19:09:27+00:00","author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-select-from-file\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-select-from-file\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"Oracle 18c – select from a flat file","datePublished":"2020-06-04T19:09:27+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-select-from-file\/"},"wordCount":610,"commentCount":0,"keywords":["19c","BI","DWH","external","features","inline","Oracle","Oracle 18c","Oracle 20c","table"],"articleSection":["Cloud","Database Administration & Monitoring","Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/oracle-select-from-file\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-select-from-file\/","url":"https:\/\/www.dbi-services.com\/blog\/oracle-select-from-file\/","name":"Oracle 18c - select from a flat file - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2020-06-04T19:09:27+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-select-from-file\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/oracle-select-from-file\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-select-from-file\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Oracle 18c – select from a flat file"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee","name":"Oracle Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","caption":"Oracle Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/14172","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/27"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=14172"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/14172\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=14172"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=14172"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=14172"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=14172"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}