External tables: querying data from flat files in oracle external tables allow oracle to query data that is stored outside the database in flat files. The oracle_loader driver can be used to access any data stored in any format that can be loaded by sql*loader. No dml can be performed on external tables but they can
be used for query, join and sort operations. Views and synonyms can be created against external tables. They are useful in the etl process of data warehouses since the data doesn't need to be staged and can be queried in parallel. They should not be used for frequently queried tables.
Basic usage
Querying the alert log
11gr2 update
Related articles.
External tables containing lob data
Basic usage
Download the files (countries1.txt, countries2.txt) containing the data to be queried. In this example the data is split across two files which should be saved to a filesystem available to the oracle server.
Create a directory object pointing to the location of the files.
Create or replace directory ext_tab_data as '/data';
Ed by newline
fields terminated
By ','
missing field
Values are null
(
Country_code char(5),
Country_name char(50),
Create the external table using the create table..organization external syntax. This defines the metadata for the table describing how it should appear and how the data is loaded.
create table countries_ext (
country_code varchar2(5),
country_name varchar2(50),
country_language varchar2(50)
)
organization external (
type oracle_loader
default directory ext_tables
access parameters (
records delimi
T country_language char(50)
)
)
location ('countries1.txt','countries2.txt')
)
parallel 5
reject limit unlimited;
Once the external table created, it can be queried like a regular table.
sql> select *
2 from countries_ext
3 order by country_name;
count country_name country_language
----- ---------------------------- -----------------------------
eng england english
fra france french
ger germany german
ire ireland english
: at line 1
sql>
Once the table is funct
Sco scotland english
usa unites states of america english
wal wales welsh
7 rows selected.
sql>
If the load files have not been saved in the appropriate directory the following result will be displayed.
sql> select *
2 from countries_ext
3 order by country_name;
select *
*
error at line 1:
ora-29913: error in executing odciexttableopen callout
ora-29400: data cartridge error
kup-04040: file countries1.txt in ext_tables not found
ora-06512: at "sys.oracle_loader", line 14
ora-0651
Jioning correctly we can create views against it.
create or replace view english_speaking_countries as
select *
from countries_ext
where country_language = 'english'
order by country_name;
select *
from english_speaking_countries;
count country_name language
----- ---------------------------- ----------------------------
eng england english
ire ireland english
sco scotland english
usa unites states of america english
4 rows selected.
sql>
Trim whitespace same as sql loader
country_n
A log of load operations is created in the same directory as the load files.
log file opened at 10/15/02 14:06:44
field definitions for table countries_ext
record format delimited by newline
data in file has same endianness as the platform
rows with all null fields are accepted
fields in data source:
country_code char (5)
terminated by ","
ame char (50)
terminated by ","
trim whitespace same as sql loader
country_language char (50)
terminated by ","
trim whitespace same as sql loader
Querying the alert log
The following example shows how an external table can be used to query the contents of the alert log.
create or replace directory bdump as '/u01/app/oracle/admin/sid/bdump/';
drop table alert_log;
create table alert_log (
line varchar2(4000)
)
organization external
(
type oracle_loader
default directory bdump
Specially useful for re
Access parameters
(
records delimited by newline
badfile bdump:'read_alert_%a_%p.bad'
logfile bdump:'read_alert_%a_%p.log'
fields terminated by '~'
missing field values are null
(
line char(4000)
)
)
location ('alert_sid.log')
)
parallel 10
reject limit unlimited
/
set linesize 1000
select * from alert_log;
11gr2 updates
Oracle 11g release 2 introduced the preprocessor clause to identify a directory object and script used to process the files before they are read by the external table. This feature was backported to 11gr1 (11.1.0.7). The preprocessor clause is
Eading compressed files, since they are unzipped and piped straight into the external table process without ever having to be unzipped on the file system.
create or replace directory exec_dir as '/bin';
create table countries_ext (
country_code varchar2(5),
country_name varchar2(50),
country_language varchar2(50)
)
organization external (
type oracle_loader
default directory ext_tables
access parameters (
records delimited by newline
preprocessor exec_dir:'gunzip' options '-c'
fields terminated by ','
missing field values are null
(
country_code char(5),
Country_name char(50),
country_language char(50)
)
)
location ('countries1.txt.gz','countries2.txt.gz')
)
No comments:
Post a Comment