· Apr 9 2m read

Foreign Tables with CSV in action

FOREIGN TABLES is a rather fresh feature in IRIS  (2023.?)
So I was motivated to try something new by own hands.
Documentation of Foreign Table from File is a good starting point.
Also the related promotional video is fine to start with.

The  advantages of the approach are pretty obvious to me

  • differently to SQL LOAD DATA data remain outside IRIS
  • you pick out what is useful to you and leave the remaining junk aside
  • this seems to be especially attractive for UPDATE and ADD-ON data
  • when you are done you have no pollution of your IRIS instance

On my way to create the example I met some facts that I want to share

  • The requirement of a JAVA language server is only mentioned in LOAD DATA
    • You fail at first access  if it is missing.  Docker Containers have it.
    • My personal environment doesn't need it.
  • Next surprise: What is this FOREIGN SERVER for?
    • Just a placeholder to point to your directory of your CSV-file
  • Creation of the TABLE is pretty straight forward and very flexible
  • Skipping USING clause presented my headers as first ROW of the table
  • And as it's no real table and had no ROW numbers I missed my numeric ID's
  • No surprise: It is READ ONLY, No Updates
  • Also: Finding the right mix of single and double quotes is some challenge
  • Similar: Data presentation in CSV can be tricky
    • Most simple approach: - thinking of date formats -
    • Declare critical columns as VARCHAR 
    • Then handle useful transformations or content check locally  
  • Finally:
    • the Table is visible for SQL but there is no related Class to it
    • you have no chance to play with CALCULATED properties


  • A great and highly useful feature
  • You have to understand the limits
  • Adjust your expectations to the available options
  • Test also the "impossible" combinations - they will pop up

Enjoy the demo package

Discussion (0)1
Log in or sign up to continue