Custom SQL Changes

This applies to: Visual Data Discovery

Changes to the way Symphony creates sources and manages source configuration creation between Composer v6.9 and Composer v7.9 include changes to the Source API. One of the primary differences is how the user interface updates the source.

  • In Composer v6.9, source updates from the user interface are done via PATCH.
  • In Composer v7.9, these changes are done via PUT. PATCH is not supported.

The changes are indicated in the table below.

Precondition Scenario: A user has created a Custom SQL source with the following fields: field1, field2, field3, field4, field5.

ADD / REMOVE / REPLACE filed Precondition:

6.9 LTS

PATCH api/sources/{sourceId}

7.10 LTS

PUT api/sources/{sourceId}

ADD - API

User updates a source with custom SQL which has fields: field1, field2, field3, field4, field5, field6.

Don't add field6 into the list of (native) fields.

SQL query is updated.

  • field6 is not added to the source fields.
  • field6 is not shown in the list of Fields.

SQL query is updated.

  • field6 is not added to the source fields.

  • field6 is shown unchecked in Available Fields on the Source Creation tab.
ADD

User updates a source with custom SQL which has fields: field1, field2, field3, field4, field5, field6.

Add field6 into the list of object/native field.

SQL query changes are applied.

  • The tables setup is updated.
  • The Fields tab shows field1, field2, field3, field4, field5, field6.
  • Other fields' attributes are not changed.
  • Chart/Global defaults are not changed.

SQL query is updated.

  • field6 is added to the source fields.
ADD - API

User updates a source with custom SQL which has fields: field1, field2, field3, field4, field5, field6.

Don’t send the list of native fields.

SQL query is updated.

  • field6 is not added to the source fields.
  • field6 is not shown in the list of Fields.

SQL query is updated.

  • field6 is added to the source fields.
REMOVE - API

field4 is not used (in filters, visuals etc.)

Don’t change the sql query, but remove field4 from the list of (native) fields.
  • field4 is removed the source fields.
  • field4 is removed from the list of Fields.
  • field4 is removed from the source.
  • field4 is displayed unchecked on the Source Creation tab.
REMOVE - API

field4 is not used (in filters, visuals etc.).

User removes field4 from the sql query and doesn’t set the list of native fields.

SQL query is updated.

  • field4 is not removed from the list of source Fields.
  • field4 is removed from the source.
  • field4 is not displayed in the list of Available Fields on the Source Creation tab.
REMOVE

field4 is not used (in filters, visuals etc.).

field4 is not greyed out on Fields tab.

User removes field field4 from the sql query.

SQL query changes are applied.

  • The field is removed from Fields tab.
  • Other fields' attributes are not changed.
  • Chart/Global defaults are not changed.
  • field4 is removed from the source.
  • field4 is not displayed in the list of Available Fields on the Source Creation tab.
REMOVE

field1 is used by Visual1.

field1 is greyed out on Fields tab.

User removes field field1 from the SQL query.

SQL query changes are applied.

  • The field is removed from Fields tab.
  • Other fields attributes are not changed.
  • Visual1 is broken.

If the user adds field1 back into the SQL query:

  • The SQL query changes are applied.
  • The field is removed from Fields tab.
  • Other fields attributes are not changed.
  • Visual1 is fixed.
 The source update fails.
REMOVE

field2 is used by Global Defaults.

field2 is greyed out on Fields tab.

User removes field2 from the SQL query.

SQL query changes are applied.

  • The field is removed from Fields tab.
  • Other fields' attributes are not changed.
  • The user can’t proceed further, and an error message appears.

If the user adds field2 back into the query, the field appears greyed out on Fields tab.

The source update fails.
REMOVE

field3 is used by Chart Defaults.

field3 is greyed out on Fields tab.

User removes field3 from the SQL query.

SQL query changes are applied.

  • The field is removed from Fields tab.
  • Other fields' attributes are not changed.
  • Chart defaults are reset.
  • Global defaults are reset.
 Not applicable.
REMOVE - Derived Field

Existing source includes derived field field1 + field4.

User updates a source with custom SQL which includes fields: field1, field2, field3, field6.

Composer is not instructed what to do with field4.
  • Tables setup is updated.
  • The Fields tab shows field1, field2, field3, field6.
  • The source cannot be saved on the Fields tab, and an error message appears.
The source update fails.
REPLACE

Existing source includes chart default with field4 used for grouping.

User updates a source with custom SQL which includes fields: field1, field2, field3, field6.

Composer is not instructed what to do with field4.
  • field6 is treated as an ADD field
  • field4 is treated as a REMOVE field.
 Not applicable.
REPLACE

Original query:

SELECT
customer_name,
order_date,
status
FROM classicmodels_tibcodv.orders

order_date and required_date have type
TIME
Option 1 query update:

SELECT
customer_name,
required_date as order_date,
status
FROM classicmodels_tibcodv.orders

  • If the name is the same, the field is treated as the same field.

  • If order_date is greyed out it will remain greyed out and used (in global defaults in this sample scenario case).

 The source is updated.
REPLACE

Original query:

SELECT
customer_name,
order_date,
status
FROM classicmodels_tibcodv.orders

order_date is type TIME
customer_country type is ATTRIBUTE

Option 2 query update:

SELECT
customer_name,
customer_country as order_date,
status
FROM classicmodels_tibcodv.orders
  • If the name is the same, the field is treated as the same field, even if the types are different.
  • Global defaults will continue using this field.
  • However, it is not possible to see the visualization for this source.

The source update fails.
CHANGE TYPE

field3 is type ATTRIBUTE.

User updates a source with custom SQL which has fields: field1, field2, field3, field6.

New field3 is NUMBER type instead of ATTRIBUTE type.

Composer is not instructed what to do with field3.

Sample query 1:

SELECT
order_date,
percent || ' %' as percent
FROM classicmodels_tibcodv.gifts

Sample query 2:

SELECT
order_date,
percent
FROM classicmodels_tibcodv.gifts

  • Composer keeps Label and Type.
  • If the original field is an attribute, but the new field is an integer – it will show it as an attribute on Fields tab. It is not possible to change the type to Integer or Number.
  • It will not be possible to create derived/custom metric (mathematical operations).

The source update fails.
CHANGE TYPE

field3 is type INTEGER.

User updates a source with custom SQL which has fields: field1, field2, field3, field6.

New field3 is type ATTRIBUTE.

Composer is not instructed what to do with field3.

  • Composer resets Label and Type.
  • The Field type is shown as ATTRIBUTE.
  • The field is still greyed out if it was used in some chart defaults.
  • Chart defaults are reset.
  • Once you save the source and open it for editing again, you can uncheck the field to remove it.
  • It will not be possible to save the source if INTEGER field was changed to ATTRIBUTE, if there were derived fields/custom metrics that require integers for mathematical operations.

The source update fails.

  • In Composer v7.10 and later, the ability to change the type is no longer supported.
  • Use the data type Convert option on the Fields tab to create a derived field with the new type.
  • INTEGER is represented as NUMBER in v7.10.