Benefits to creating foreign table views include the following:
- Security
- Hiding SQL complexity
- Easier querying of external object storage data with inconsistent uppercase and lowercase field names.
CSV field names are case sensitive, but Teradata SQL is case insensitive.
- Hiding the $path references in a foreign table and making path names look like standard column names.
Follow these recommendations when creating a view on a foreign table:
- Include both path names and payload attributes as columns in the view.
- CAST all values in the view to an appropriate data type.
- Often the path segment name (such as site_no in data/site_no/year/month/day) is also represented by an attribute in your data, in which case Teradata recommends you equate the path segment name and data attribute in the WHERE expression in the view and specify only one in the column list.