Skip to content
#Architecture

[2/2] Providing quality ETLs on Vertica - Alexander Krasheninnikov - SmartData 2023 (Category Architecture)

#Architecture #Data #DWH #Processes #Management #Software #SoftwareArchitecture

First. posture This report did not fit all the practical advice of Sasha, so we will continue.

To build analytics, it is worth building an OLAP cube with components Measurements: time, account, resource group, cluster node Indicators: sum/skew/avg/q50/q75/q90/q99 by query metrics Time: CPU, total time

  • RAM Data volumes: read/write on disk, read/write over the network
  • Number of requests
  • COST

Well, then work with defects - when problems have similar patterns, which can be divided into Static - the data is calm, but it will hurt when you have to work with them (tables, columns) Dynamic - it's not working optimally right now (requests and sequences of requests)

Static data defects Storage and processing of text information in VARCHAR - if we ask VARCHAR(2048)And we write there. 2 We have additional overheads, and when using UDF (user defined functions) We'll actually take all 2kb even if we write it. 2 symbol numeric with the control of the accuracy of the number - more accuracy, more space on the disk, and if the types do not match, we spend CPU and RAM to bring them. Usually enough numeric(18, 4) into 90percentage (Which takes up space as an int64) Sorting – It’s important to sort by a smaller field + too many fields to sort can be a mistake

  • Data distribution (sharding) Sharding on a smaller field + proper use of non-segmented tables. Segmentation by float can be a mistake. (due to floating accuracy and distribution of close values on different nodes)
  • Partitioning - on the correct field + large partitions in not too many parties, if small, then we do not particinate
  • Data distortion - skewed data slows down all requests, it is necessary to fight with distortions. Sasha shows how to do it with NULL fields
  • And a lot of problems.

Dynamic data defects Dynamic data defects can be associated with vulnerabilities and bad smell in existing queries. For example, if a request generates a lot of temporary data, it could be a sign of an error and the request could be shot down sooner or later. If a request transmits a lot of data over the network, it can also be a sign of an error, and the request can be shot for it. If the request updates data during execution and it exceeds a certain size, it may be a sign of an error, and the request may be optimized to use an incremental update.

Optimization of requests The total reading of data does not scale, it is necessary to optimize the process and switch to incremental updating. Data distortion can lead to performance problems. It is important to review the process if it does not scale.

Productivity problems The amount of temporary data on a single node can cause performance problems. Implicit commits and multiple sql instructions can cause errors and false positives. It is important to teach users the rules of the game and the boundaries of what is allowed.

Findings Quantify resources at the start Define the rules of the database game and the limits of what is allowed -Train users - documentation, workshops, ... Collect analytics on process activities Don’t be afraid to shoot down troublesome processes. Create transparency for users about their processes

#Data #DWH #Processes #Management #Architecture #Software #SoftwareArchitecture