Select your cookie preferences

We use essential cookies and similar tools that are necessary to provide our site and services. We use performance cookies to collect anonymous statistics, so we can understand how customers use our site and make improvements. Essential cookies cannot be deactivated, but you can choose “Customize” or “Decline” to decline performance cookies.

If you agree, AWS and approved third parties will also use cookies to provide useful site features, remember your preferences, and display relevant content, including relevant advertising. To accept or decline all non-essential cookies, choose “Accept” or “Decline.” To make more detailed choices, choose “Customize.”

AWS Logo
Menu
Create External Tables using Amazon EFS with Amazon RDS for Oracle

Create External Tables using Amazon EFS with Amazon RDS for Oracle

Amazon RDS for Oracle has the ability to integrate with Amazon Elastic File System (Amazon EFS). In this post I share guidance on how to create Oracle external tables using EFS file system.

databasesrdsoracleefs
Yossi Lagstein
Amazon Employee
Published Oct 26, 2024
Comments
Amazon RDS for Oracle has the ability to integrate with Amazon Elastic File System (Amazon EFS). Integrate Amazon RDS for Oracle with Amazon EFS blog post includes detailed explanation on how to perform the integration. In addition it covers multiple use cases of having Amazon RDS for Oracle utilize Amazon EFS . In this post I share another use case, using Amazon EFS as file system for Oracle external tables.

Create External Tables using Amazon EFS

Oracle external tables enables you to access data stored in an external file. You can query the data as it if was stored in a table in the database. The following are list of steps to create an external table with an external file located in Amazon EFS.
NOTE: As pre-requisite you should follow the guidance in the blog post to create an Amazon EFS file system, integrate it with Amazon RDS for Oracle and have an Amazon EC2 server with Amazon EFS file system mounted as /efsdir
Image not found
Amazon RDS for Oracle with Amazon EFS
1/ Login to the Amazon EC2 server where you have mounted the Amazon EFS volume. Create a directory on the Amazon EFS file system and change its permissions
1
2
sudo mkdir /efsdir/external
sudo chmod -R 777 /efsdir/external
2/ Create a text file called basketball_teams.txt at /efsdir/external and add the some rows using the following command.
1
echo -e 10, Boston\\n20, Denver\\n30, Toronto > /efsdir/external/basketball_teams.txt
3/ Connect to the database and create an Oracle Directory.
1
2
3
4
5
6
BEGIN
rdsadmin.rdsadmin_util.create_directory_efs(
p_directory_name => 'EXTERNAL_DIR_EFS',
p_path_on_efs => '/rdsefs-fs-05cef2152acda175/external');
END;
/
4/ Create and external table, make sure do disable Direct NFS , as Amazon EFS does not support direct NFS
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
CREATE TABLE basketball_teams (
id NUMBER,
team_name VARCHAR2(50)
)
ORGANIZATION EXTERNAL (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY EXTERNAL_DIR_EFS
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
DNFS_DISABLE
FIELDS TERMINATED BY ','
MISSING FIELD VALUES ARE NULL
(id,team_name)
)
LOCATION ('basketball_teams.txt')
)
PARALLEL
REJECT LIMIT UNLIMITED;
5/ You can now query the table
1
select * from basketball_teams;
 

Any opinions in this post are those of the individual author and may not reflect the opinions of AWS.

Comments

Comments

Log in to comment