MySQL as Analytical Storage

You are looking at an older version of the documentation. The latest version is found here.

Data Virtuality Server's Analytical Storage can also be powered by MySQL. Here are several points to keep in mind:

1. This is applicable only if you are using an older version of the Data Virtuality Server, below 2.4.7

   1. MySQL has a hard limit with regard to the maximum number of columns per table and the maximum size of a row (see MySQL documentation for more detail). While the limit of 4096 columns per table is rarely exceeded, materializing strings can easily hit the limit of 65535 bytes per row, often in the context of web connectors.

      1. If a string is not explicitly cast to a specific length, a default length of 4000 characters is applied. It is therefore important to limit the string or clob length when creating materialized views. Since the maximum string length is unknown when working with data from web connectors like Google Analytics or files, such as CSV, XML or JSON, Data Virtuality Server will not implicitly apply any length restrictions.
         Here is an example of restricting the length using CAST:

         SQL

          SELECT 
         		CAST(a.sessions as long) AS "sessions"
         		 ,CAST(a.hits as long) AS "hits"
         		 ,CAST(a.browser as string(100)) AS "browser"
         		 ,CAST(a.operatingSystemVersion as string(100)) AS "operatingSystemVersion"
         		 ,CAST(a.mobilDeviceInfo AS string(100)) as "mobileDeviceInfo"
         	FROM
         		analytics.profiles p
         		,table (
         			CALL analytics.get (
         				profile => p.id
         				,startDate => '2015-01-21'
         				,endDate => '2015-12-21'
         				,metrics => 'sessions,hits'
         				,dimensions => 'browser,operatingSystemVersion,mobilDeviceInfo'
         			)
         		) a;;

      2. When materializing views with a considerable amount of columns, you might be able to materialize each participating view or table, but not the JOIN itself. This leads to the JOIN being calculated at runtime by the Data Virtuality Server. Using CAST to resolve this issue will not work.
      3. The Data Virtuality Server offers a workaround to avoid failures on materializing strings whose length can exceed the limit of 65535 bytes per row. Two translator properties can be used when creating the Analytical Storage:
         • createStringsAsClobs=TRUE which forces the creation of clob columns instead of string columns in the Analytical Storage;
         • stringClobsLengthThreshold which determines the minimal size of the string column to be converted to a clob column. The default for stringClobsLengthThreshold is 64 bytes.
           Thus, if the createStringsAsClobs=TRUE translator property is used in the MySQL analytical storage, all string columns with a size greater than 64 bytes will be converted to clob columns when materialized. To override the 64 bytes limit and set it to 256 bytes, for example, a combination of createStringsAsClobs=TRUE, stringClobsLengthThreshold=256 should be used.
2. Only MySQL 8 and later support window functions that are often relevant for BI purposes. For older versions, the Data Virtuality Server must emulate the functionality since it does not exist in MySQL. This leads to degraded performance and higher resource consumption. Support for MySQL 8 in the Data Virtuality Server is currently still under development.
3. Poor performance of DELETE queries. MySQL shows very poor performance of certain DELETE queries when used in conjunction with subqueries since it fails to remember subquery results and instead re-evaluates it for every row considered for deletion. This can cause severe load problems and runtime issues on the server for large tables, thus burdening administrators.