Chapter 14
Download
1 / 17

Chapter 14 - PowerPoint PPT Presentation


  • 96 Views
  • Uploaded on

Chapter 14. External Tables. External Table Features. A n external table allows you to create a database table object that uses as its source an operating system file .

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' Chapter 14' - larue


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Chapter 14

Chapter 14

External Tables


External table features
External Table Features

  • An external table allows you to create a database table object that uses as its source an operating system file.

  • Directly select information from operating-system flat files via SQL, which allows you to do tasks such as loading operating-system comma-separated-value (CSV) files into the database.

  • Create platform-independent dump files that can be used to transfer data. You can also create these files as compressed and encrypt them for efficient and secure data transportation.


Sql loader versus external tables
SQL*Loader versus External Tables

  • Loading data with external tables is more straightforward and requires fewer steps.

  • The interface for creating and loading from external tables is SQL*Plus. Many DBAs/developers find using SQL*Plus more intuitive and powerful than SQL*Loader’s parameter-file interface.

  • You can view data in an external table before it’s loaded into a database table.

  • You can load, transform, and aggregate the data without an intermediate staging table. For large amounts of data, this can be a huge space savings.


Loading an external table from a csv file
Loading an External Table from a CSV File

  • Create a database-directory object that points to the location of the CSV file.

  • Grant read and write privileges on the directory object to the user creating the external table. I usually use a DBA privileged account, so I don’t need to perform this step.

  • Run the CREATE TABLE...ORGANIZATION EXTERNAL statement.

  • Use SQL*Plus to access the contents of the CSV file.



Creating external table statement
Creating External Table Statement

create table exadata_et(

exa_id NUMBER

,machine_count NUMBER

,hide_flag NUMBER

,oracle NUMBER

,ship_date DATE

,rack_type VARCHAR2(32)

)

organization external (

type oracle_loader

default directory exa_dir

access parameters

(

records delimited by newline

fields terminated by '|'

missing field values are null

(exa_id

,machine_count

,hide_flag

,oracle

,ship_date char date_format date mask "mm/dd/yyyy"

,rack_type)

)

location ('ex.csv')

)

reject limit unlimited;


Viewing external table metadata
Viewing External-Table Metadata

  • Use the DBA_EXTERNAL_TABLES, DBA_EXTERNAL_LOCATIONS views:

    select

    owner

    ,table_name

    ,default_directory_name

    ,access_parameters

    from dba_external_tables;

    select

    owner

    ,table_name

    ,location

    from dba_external_locations;


Load a regular table with data from an external table
Load a Regular Table with Data from an External Table

  • Use APPEND hint to direct path load.

  • Very efficient way to transfer data from an external table to a regular table where the data can be manipulated.

    SQL> insert /*+ APPEND */ into exa_info select * from exadata_et;


Performing advanced transformations
Performing Advanced Transformations

  • Create an external table.

  • Create a record type that maps to the columns in the external table.

  • Create a table based on the record type created in Step 2

  • Create a piplelined function that is used to inspect each row as it’s loaded and transform data based on business requirements.

  • Use an INSERT statement that selects from the external table and uses the pipelined function to transform data as it’s loaded.


Basing an external table on an os text file
Basing an External Table on an OS Text File

  • This example bases an external table on the alert.log file.

  • This allows users to query the alert.log file via SQL*Plus


Basing an external table on an os text file1
Basing an External Table on an OS Text File

create table alert_log_file(

alert_text varchar2(4000))

organization external

( type oracle_loader

default directory t_loc

access parameters (

records delimited by newline

nobadfile

nologfile

nodiscardfile

fields terminated by '#$~=ui$X'

missing field values are null

(alert_text)

)

location ('alert_O11R2.log')

)

reject limit unlimited;



Using parallelism when unloading
Using Parallelism when Unloading

CREATE TABLE inv_et

ORGANIZATION EXTERNAL (

TYPE ORACLE_DATAPUMP

DEFAULT DIRECTORY dp

LOCATION ('inv1.dmp','inv2.dmp')

)

PARALLEL 2

AS SELECT * FROM inv;


Compressing a dump file
Compressing a Dump File

ACCESS PARAMETERS clause:

CREATE TABLE inv_et

ORGANIZATION EXTERNAL (

TYPE ORACLE_DATAPUMP

DEFAULT DIRECTORY dp

ACCESS PARAMETERS (COMPRESSION ENABLED)

LOCATION ('inv1.dmp')

)

AS SELECT * FROM inv;


Encrypting a dump file
Encrypting a Dump File

CREATE TABLE inv_et

ORGANIZATION EXTERNAL (

TYPE ORACLE_DATAPUMP

DEFAULT DIRECTORY dp

ACCESS PARAMETERS

(ENCRYPTION ENABLED)

LOCATION ('inv1.dmp')

)

AS SELECT * FROM inv;


Preprocessing an external table based on a zipped os file
Preprocessing an External Table Based on a Zipped OS File

create table exadata_et(

machine_count NUMBER

,hide_flag NUMBER

,oracle NUMBER

,ship_date DATE

,rack_type VARCHAR2(32)

)

organization external (

type oracle_loader

default directory data_dir

access parameters

(

records delimited by newline

preprocessor exe_dir: 'gunzip'

fields terminated by '|'

missing field values are null

(exa_id

,machine_count

,hide_flag

,oracle

,ship_date char date_format date mask "mm/dd/yyyy"

,rack_type)

)

location ('ex.csv.gz')

)

reject limit unlimited;


Summary
Summary

  • External tables are flexible objects that allow you to create a database table that uses an operating system file as its input.

  • Allows for easy loading of data from CSV and text files.

  • External tables can also be used to efficiently and securely transfer data from one database environment to another.


ad