|External Tables - New in Db2|
Overview: External TablesAccording to the Db2 documentation for external tables, it is a table that resides in a text-based file outside the Db2 database. The external table can be used to
- store data outside the database and still be able to query it,
- to easily load data from outside and apply some additional features like casts or joins with other data or omit columns,
- share and transfer data to applications.
External Table with Cloud Object StorageFor my first test I put a small delimited file testdata.csv up into my Cloud Object Storage with Swift API. Then, I created the external table exttabl1 pointing to that file:
CREATE EXTERNAL TABLE exttab1(a int,s varchar(50)) using
The above is similar to a regular CREATE TABLE. It has the column definitions followed by USING with some options. Via DATAOBJECT I tell Db2 to use my test file. The option DELIMITER is needed because the default delimiter is '|' (the vertical bar). I ran into some errors with my truly comma-separated values.
The last option, SWIFT, has several parts to provide the access URI as well as username, password and the storage container / bucket. To utilize S3 storage, replace "swift" by "s3" and provide similar credentials. It is easy. Once the table is created, it can be queried:
select * from exttab1;
Business or SQL as usual. It is not possible to update, delete or truncate an external table. However, I was able to insert into it and thereby replacing the entire file content. Thus, use it with caution.
When the external table is accessed, Db2 provides diagnostic information in log files. The files can be placed via the LOGDIR or ERROR_LOG option. By default the log output is written into the same location as the source file for the external table. That additional I/O and data might be unexpected to many (I was surprised, too). It should be placed somewhere else and cleaned up on a regular basis.
ConclusionsExternal tables provide a simple, SQL-based mechanism to interface with external data, both to incorporate it into the Db2 database or to export it for other use. Storage options include "the cloud" with the popular Swift and S3 APIs. I am going to test external tables further and so should you...
If you want to learn more about that topic or discuss the blog with me, then meet me at the upcoming IDUG EMEA and Db2 Aktuell conferences. If you have feedback, suggestions, or questions about this post, please reach out to me on Twitter (@data_henrik) or LinkedIn.