Skip to main content

schema on read and Schema on write

Schema on read  and Schema on write

Schema on Read :  Write your data first, figure out what it is later. 
Hive (in some cases), Hadoop, and many other NoSQL systems in general are about "schema on read" -- the schema is applied as the data is being read off of the data store
Benefits of schema on read:
  • Flexibility in defining how your data is interpreted at load time
    • This gives you the ability to evolve your "schema" as time goes on
    • This allows you to have different versions of your "schema"
    • This allows the original source data format to change without having to consolidate to one data format
  • You get to keep your original data
  • You can load your data before you know what to do with it (so you don't drop it on the ground)
  • Gives you flexibility in being able to store unstructured, unclean, and/or unorganized data
Downsides of schema on read:
  • Generally it is less efficient because you have to reparse and reinterpret the data every time (this can be expensive with formats like XML)
  • The data is not self-documenting (i.e., you can't look at a schema to figure out what the data is)
  • More error prone and your analytics have to account for dirty data

 Schema on write : Figure out what your data is first, then write it after.
A traditional relational database stores the data with schema in mind. It knows that the second column is an integer, it knows that it has 40 columns, etc. Therefore, you need to specify your schema ahead of time and have it well planned out. This is "schema on write" -- that is, the schema is applied when the data is being written to the data store.
Benefits of schema on write:
  • Better type safety and data cleansing done for the data at rest
  • Typically more efficient (storage size and computationally) since the data is already parsed
Downsides of schema on write:
  • You have to plan ahead of time what your schema is before you store the data (i.e., you have to do ETL)
  • Typically you throw away the original data, which could be bad if you have a bug in your ingest process
  • It's harder to have different views of the same data

Labels:

Comments

Post a Comment

Popular posts from this blog

Learn GitHub

Learn GitHub git init git add file.txt git commit -m "my first commit" git remote add origin https://github.com/dansullivanma/devlops_data_sci.git git clone https://github.com/dansullivanma/devlops_data_sci.git
Post a Comment
Read more

Garbage collection in Databricks

Clean up snapshots Delta Lake provides snapshot isolation for reads, which means that it is safe to run  OPTIMIZE  even while other users or jobs are querying the table. Eventually however, you should clean up old snapshots. You can do this by running the  VACUUM  command: VACUUM events You control the age of the latest retained snapshot by using the  RETAIN   <N>   HOURS  option: VACUUM events RETAIN 24 HOURS Test the garbage collection You can specify  DRY   RUN  to test the garbage collection and return a list of files to be deleted: VACUUM events DRY RUN Configure the retention threshold The  VACUUM  command removes any files that are no longer in the latest state of the transaction log for the table and are older than a retention threshold. The default threshold is 7 days, but you can specify an alternate retention interval. For example, to delete all stale files older t...
Post a Comment
Read more

Z-Ordering

Z-Ordering in Databricks Z-Ordering is a technique to colocate related information in the same set of files. This co-locality is automatically used by Delta Lake on Databricks data-skipping algorithms to dramatically reduce the amount of data that needs to be read. To Z-Order data, you specify the columns to order on in the  ZORDER   BY  clause: OPTIMIZE events WHERE date >= current_timestamp () - INTERVAL 1 day ZORDER BY ( eventType ) You can specify multiple columns for  ZORDER   BY  as a comma-separated list. However, the effectiveness of the locality drops with each additional column. Z-Ordering on columns that do not have statistics collected on them would be ineffective and a waste of resources as data skipping requires column-local stats such as min, max, and count. You can configure statistics collection on certain columns by re-ordering columns in the schema and/or increasing the number of columns to collect s...
Post a Comment
Read more