Kim Berg Hansen

Kim Berg Hansen is a database developer from Middelfart in Denmark. Originally wanting to work with electronics; he almost coincidentally tried computer programming and discovered where his talent lay; as the programs he did worked well – unlike the electronics projects he soldered that often failed. After that experience he progressed from Commodore Basic on VIC-20 over Modula-2 and C at Odense University to Oracle SQL and PL/SQL; which last two languages he now has worked with extensively since the year 2000. His professional passion is to work with data inside the database utilizing the SQL language to the fullest to achieve the best application experience for the users.

Kim shares his experience and knowledge by writing the Practical Oracle SQL book; blogging; presenting at various Oracle User Group conferences; and being the SQL quizmaster at the Oracle Dev Gym. His motivation is when peers go “now I understand” after his explanations; or when end users “can’t live without” his application coding. He is certified Oracle OCE in SQL as well as awarded Oracle ACE Director. Outside the coding world Kim is married; loves to cook; and is a card-carrying member of Danish Beer Enthusiasts association.

During ConTech2021 Kim Berg Hansen will present External Tables – Not Just Loading a CSV File

Abstract:
With external tables you can SELECT from files – great for loading plain CSV files; but it can do so much more than that. Learn about loading from text files; DataPump dump files; HDFS and HIVE; as well as how to add constraints and partitioning for optimizer optimizations of the external tables.
Description:
External tables are a way to map data from files in the OS to columns in a rowsource – in effect allowing you to SELECT directly from a file. This can be useful in many ways allowing you to for example join the external data to lookup tables in the database; as well as use the complete range of SQL syntax and functions to transform the external data if needed.

From version to version; Oracle database has added functionality to the external table; so it now is capable of so much more than simply loading a CSV file. You can load data from flat text files; from binary DataPump dump files; from HDFS (Hadoop Distributed File System) or from Apache HIVE. You can even do a limited unloading to a DataPump dump file. You can have the loading process perform an OS preprocess call; like for example unzip to allow you to load from a compressed file.

Oracle 12.2 also adds the possibility of adding certain metadata to the external table useful for the optimizer. You can define constraints (primary key; referential; unique; not null) for the optimizer to trust and rely on; and you can define partitions to allow partition pruning to read only the necessary files of a multi-file external table.

This presentation discusses the various possibilities and shows examples of syntax and use cases for the various access drivers – ORACLE_LOADER; ORACLE_DATAPUMP; ORACLE_HDFS and ORACLE_HIVE.