At my company, we are researching a lot the DV 2.0 data model and making some PoCs, but there isn't a lot of experiences on the web. I'm concerned about data replication (keeping data history in the Enterprise layer, almost replicating data in our data lake). Even though is not exclusively DV related, joins are costly and time-consuming with Hive and even with Impala. We already developed pyspark applications to reduce the time of this joins, getting interesting improvements, trying to get better times for constructing the staging, enterprise and data access layers. We are already using parquet files and partitioning.   I would appreciate any experience you can share with me ... View more

Hello,   I need some guidance on how to model the partitions of headers and details of invoices using Hive. Header tables I plan to store partitioned by year, month and day (e.g., year=2019/month=12/day=2)   Detail table: Around 200 million records per day) Have invoice number (unique) to relate to headers, but no dates included in the detail per se   I am thinking about 2 options: Partitioning also by year, month, day. This needs a join to be performed in the data ingestion process, before storing. In this scenario, joins between header and details could be penalized because the join is based on the invoice number Partitioning by a fixed amount of characters of the invoice number (unique). (e.g., for invoice number 123456789 a partition would be "12345"). I think this approach could be better because invoice numbers are sequential and thus "tightly" mapped with the dates (partition scheme of header table) I also need to manage corner (dirty) cases, like invoice details with no valid invoice id: I'm thinking about a dedicated partition for these cases   Does somebody have some suggestions or experience with a related scenario?   Thanks a lot ... View more

Hello,   I need some guidance on how to model the partitions of headers and details of invoices using Hive.   Header tables I plan to store partitioned by year, month and day (e.g., year=2019/month=12/day=2)   Detail table: Around 200 million records per day) Have invoice number (unique) to relate to headers, but no dates included in the detail per se   I am thinking about 2 options for detail table: Partitioning also by year, month, day. This needs a join to be performed in the data ingestion process, before storing. In this scenario, joins between header and details could be penalized because the join is based on the invoice number Partitioning by a fixed amount of characters of the invoice number (unique). (e.g., for invoice number 123456789 a partition would be "12345"). I think this approach could be better because invoice numbers are sequential and thus "tightly" mapped with the dates (partition scheme of header table) I also need to manage corner (dirty) cases, like invoice details with no valid invoice id: I'm thinking about a dedicated partition for these cases   Does somebody have some suggestions or experience with a related scenario?   Thanks a lot ... View more