�ݺ�ߣ

Editor's Notes

• #2: Title:?Schema?from Nothing: Transformation of JSON into PostgreSQL 9.4 SQL+JSONB Power?Schema.  Speaker Name: Christopher W.V. Hogue, Ph.D., Senior Data Scientist, Joyent Inc.  Abstract: The new PostgreSQL 9.4b JSONB feature allows us to re-imagine a strategy for transforming arbitrarily structured JSON key-value data into an SQL queryable form. An automated approach to a new normalization strategy has been developed which generates mixed columns of native SQL types and JSONB. This approach is implemented in a set of open source tools used to transform several JSON sets stored in key-value systems and combine them into an SQL data warehouse for cloud operations reporting at Joyent. The transformation handles the two edge cases of sparse and repetitive JSON data using a generic table structure called a Power?Schema. Classification of JSON keys into the Power?Schema?columns uses a heuristic based on Shannon Information. The Power?Schema?table structure maintains the flexibility of JSON and?schema-free input data by allowing storage of unanticipated JSON key-value pairs that may later appear in data. Base SQL data types are inferred with streaming tools that aggregate type and size information across the input JSON, and this can operate in Map/Reduce mode. A hierarchy of types promotes JSON singleton or array columns into native PostgreSQL types containing integers, floating point numbers, UUIDs, timestamps, mac addresses, and IP addresses. These are preferred to JSON array forms according to a fail-early philosophy: base SQL types are validated and type cast at load time, whereas the contents of JSONB typed JSON arrays are not - leading to late failures at query time. The transformed reporting database integrates several separate JSON key-value stores and offers straightforward connectivity and query with SQL-based dashboarding and reporting tools, including R and Tableau.?   Twitter Handle: @cwvhogue
• #3: SQL Normalization is set up to deal with anomalies in transaction processes. Prevent data inconsistency. Transactions done in JSON �C machine generated logs, system updates, system state information, time series state, current state. Conventionally, the data modeler should fully normalize, then denormalize for performance. What to do when there is no a-priori data model, no normalization, just piles of JSON?
• #4: SQL Normalization is set up to deal with anomalies in transaction processes. Prevent data inconsistency. Transactions done in JSON �C machine generated logs, system updates, system state information, time series state, current state. Conventionally, the data modeler should fully normalize, then denormalize for performance. What to do when there is no a-priori data model, no normalization, just piles of JSON?
• #5: Toy example that is in fact generalizable �C single characters �C but could be ANY type of data
• #6: Zipfian, Pareto, don��t care what kind of power-law it is In general collections of data have common varying elements, repetitive elements and rare elements.
• #10: This effects a data compression scheme, removed tags, repeating values, and mostly-null columns...