Lab
Lab #9: External Tables
Learn how to create an externally managed table and how to run anonymous block PL/SQL programs to fix data states in the some of the tables. You create the airport and account_list tables and their respective airport_s1 and account_list_s1 sequences before running the anonymous block PLSQL programs. You also insert six rows into the common_lookup table.
Objectives
You learn how to create an external read-only transaction_upload table, which is a primary technique for importing large data sources into the Oracle database. External tables support comma-separated values (CSVs), tab-separated values (TSVs), and position specific files as read-only sources for large data imports.
- Learn how to create an external
transaction_updatetable that uses a comma-separated values (CSVs) file structure. - Learn how to query results from a read-only external
transaction_uploadtable.
In support of the objective, you create a transaction target table and seed six supporting rows in the common_lookup table. You also add the airport and account_list tables and populate the two new tables with with data by using anonymous block PL/SQL programs.
The use of external tables in any import or export solution combines the skill set of a system administrator, database administrator, and programmer or developer. A programmer works on a document of detailed specification whereas a developer works on a general idea or loosely detailed specification.
The system administrator focuses on defining the flat file, putting it in the right directory, and assigning the file the correct file privileges. The database administrator (DBA) coordinates with the system administrator on the structure of the flat file and creates the external table to read the file. The DBA also ensures the external table has the necessary privileges to read the external file. The developer writes the program that performs the Extract, Transform, and Load (ETL) operations. The means the developer writes the query against the external table and uses a MERGE statement or procedural program to write the dat to the final target tables.
Business Scenario
Business analysts design approaches to move data from one source to another. The most common format for moving files are flat files. Flat files have many formats but the most common formats are:
- Comma-separated values (CSVs) files
- These files are tables of a record structure that is tab-delimited into fields. Fields correlate to column values in a relational model. Fields are frequently enclosed in double quotes when they contain a single quote or apostrophe or a whitespace. Microsoft Excel uses CSV files as its default approach to export and import files.
- Tab-separated values (TSVs) files
- These files are tables of a record structure that is comma-delimited into fields. Fields correlate to column values in a relational model. Fields are frequently enclosed in double quotes when they contain a single quote or apostrophe or a whitespace. Microsoft Excel uses TSV files asas its default approach to export and import files.
- Position specific files
- These files are tables of a record structure that is mapped into position specific fields. Fields correlate to column values in a relational model but must always start and end at a specific position in each row. Fields typically do not get enclosed in double quotes when they contain a single quote, or apostrophe, or a whitespace. Microsoft Excel has the ability to read position specific files.
Overview
The lab is designed to teach you how to perform ETL from the perspective of a pre-existing flat-file. The data would have been extracted from a prior source into the flat file and uploaded to the server where the external table can read it and transform and load the data into its destination tables.
