Uploaded image for project: 'Teiid'
  1. Teiid
  2. TEIID-5965

Allow variables to be used as TextTable delimeters, row delimeters, quote, header, skip rows, and escape characters

    Details

    • Type: Enhancement
    • Status: Open (View Workflow)
    • Priority: Minor
    • Resolution: Unresolved
    • Affects Version/s: 13.1
    • Fix Version/s: Backlog
    • Component/s: Query Engine
    • Labels:
      None
    • Story Points:
      1

      Description

      In the specified example, the delimiter is TAB. Many web APIs allow customization of the delimiter character, and in order to provide a generic parser, sometimes it is way easier to define a delimiter as a variable, and not need to create a long nested structure with IF-THEN-ELSE-IF-ELSE constructs:

      Select * From TextTable (
      	'c1	c2
      1	2'
      	Columns
      		c1 integer,
      		c2 integer
      	Delimiter E'\t'
      	Header 1
      )x;
      

      Imagine that based on setup on API side (out of our control) the content can be delivered via tab or semicolon, e.g.

      c1;c2
      1;2
      

      or

      c1	c2
      1	2
      

      Let's save this response into a variable and see the code, which we will need to write depending on the setup:

      Begin
          ...
          If (delimiter = 'tab')
          Begin
              Select * From TextTable (
                  apiResponse
                  Columns
                      c1 integer,
                      c2 integer
                  Delimiter E'\t'
                  Header 1
              )x;
          End
          Else If (delimiter = 'tab')
          Begin
              Select * From TextTable (
                  apiResponse
                  Columns
                      c1 integer,
                      c2 integer
                  Delimiter ';'
                  Header 1
              )x;
          End
      End
      

      The if-else block is constantly growing, especially if we want to customize quote, escape, delimiter, and row delimiter.
      Thus it would be great if we could make these values configurable. In this case, we could end up with this expected code, which is more readable and more easily customizable:

      Begin
          ...
          Declare string delimiter = E'\t';
          Select * From TextTable (
              apiResponse
              Columns
                  c1 integer,
                  c2 integer
              Delimiter delimiter
              Header 1
          )x;
      End
      

      With a bit of tweaking and certain assumptions, leading in the trust level, we can even read the first line and try to auto-detect the delimiter automatically (e.g. by counting tabs, commas, and semicolons in the first line).

      Please, could you be so kind as to make HEADER and SKIP values customizable (rather than hardcoded numbers)?

        Gliffy Diagrams

          Attachments

            Activity

              People

              • Assignee:
                shawkins Steven Hawkins
                Reporter:
                dalex005 Dmitrii Pogorelov
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:

                  Time Tracking

                  Estimated:
                  Original Estimate - 5 hours
                  5h
                  Remaining:
                  Time Spent - 1 hour Remaining Estimate - 4 hours
                  4h
                  Logged:
                  Time Spent - 1 hour Remaining Estimate - 4 hours
                  1h