![\"\"](\"https:\/\/i0.wp.com\/loicmathieu.fr\/wordpress\/wp-content\/uploads\/Pinot-architecture.jpg?resize=606%2C475&ssl=1\")
\nI’ve been wanting to test Apache Pinot for a very long time and I finally took the time to do it!<\/p>\n
Pinot is a real-time distributed OLAP datastore, purpose-built to provide ultra low-latency analytics, even at extremely high throughput. It can ingest directly from streaming data sources or batch data sources.\nAt the heart of the system is a columnar store, with several smart indexing and pre-aggregation techniques for low latency.\nPinot was built by engineers at LinkedIn and Uber and is designed to scale up and out with no upper bound. Performance always remains constant based on the size of your cluster and an expected query per second (QPS) threshold.<\/p>\n\n
A Pinot cluster consists of the following elements:<\/p>\n
So let’s start at the beginning: launching Pinot locally! Apache Pinot being a distributed system with several components (Zookeeper, Pinot Controller, Pinot Broker, Pinot Server), I decided to go through an all-in-one Docker image to test it locally, it seems to be the simplest way.<\/p>\n
Based on the guide Getting Started <\/a>, I run Pinot with this Docker command which allows it to be started with a pre-imported Baseball stat dataset. The container, after having instantiated the components and imported the data, will then execute a set of queries and display their results in the logs.<\/p>\n After starting Pinot and importing the dataset, I can use the Pinot console (available on port 9000 by default) to access the cluster.<\/p>\n This allows you to view the status of the cluster via the Cluster Management<\/strong> tab.<\/p>\n And launch queries via the Query Console<\/strong> tab. I make a Alright, that’s nice, but to get past Hello World and really test Pinot, I’m going to need a bigger dataset to play with.<\/p>\n The European Commission provides a set of Open Data, now is the time to take advantage of it.<\/p>\n I choose a dataset on greenhouse gas emissions in the Eurozone (you can find it at this page<\/a>): Air emissions accounts by NACE Rev. 2 activity<\/a> of 4 million data points.<\/p>\n Here is the description of the dataset: This data set reports the emissions of greenhouse gases and air pollutants broken down by 64 industries (classified by NACE Rev. 2) plus households. Concepts and principles are the same as in national accounts. Complete data starts from reference year 2008<\/em>.<\/p>\n We will now have to load the data: each column is separated by a tabulation, the absence of data is defined by To send this data to Pinot, I relied on the guide Pushing your data to Pinot<\/a>.<\/p>\n To start, you have to define a schema for the data, here is the one I used:<\/p>\n In this schema, we define two types of fields, dimension fields: these are strings on which we will be able to filter or group the data, and metric fields which are here all floats (one field per year of data available) on which we will be able to make calculations (aggregations).<\/p>\n You must then define the table where to store the data, here is the definition of the table, it is for the moment very straightforward:<\/p>\n To be able to create this table, I restarted a Pinot cluster from the Docker Compose proposed in the documentation<\/a>, then used the following Docker command which will launch a table creation command with the schema and table definitions previously created:<\/p>\n We can then check via the Pinot console (http:\/\/localhost:9000<\/a>) that the table has been created.<\/p>\n Now, time to insert the data!<\/p>\n The file is a TSV and contains The ingestion into Pinot is done via an ingestion job defined in yaml format.<\/p>\n This job defines among other things:<\/p>\n To launch the job, you can use the following Docker command:<\/p>\n After ingestion, we now have 266456 rows in our table which we can then query from the Pinot console.<\/p>\n For example via the following query: Pinot uses Apache Calcite for querying, so we can use ANSI SQL which greatly simplifies querying. The above query will return the following results:<\/p>\n Since the dataset is quite small, queries run in milliseconds despite no index have been created.<\/p>\n Now let’s try the following query: Pinot has therefore scanned 66614 documents out of 266456, the documents which correspond to the G_HAB unit. During the filter phase, it scanned 266456 entries, it did a full scan on the table.<\/p>\n Pinot allows the addition of indexes to optimize the queries, in the query previously used, the unit column was used to filter the data.<\/p>\n So I’m going to modify the table structure to add an inverted index on the unit column.<\/p>\n To do this we can modify the description of the table to add this index:<\/p>\n For simplicity, I used the Pinot GUI to add the index (by editing the table) and then reload the segments. Reloading segments after a table change is necessary for the index to be created and updated.<\/p>\n After relaunching the query, we see that the numEntriesScannedInFilter<\/strong> goes to 0, Pinot is now using the newly created index.<\/p>\n One of the strengths of Pinot is that it supports many different types of indexes, which makes it possible to implement different use cases, and to optimize for query or storage, each index using disk space. .<\/p>\n To go further on Pinot’s indexing capabilities, you can read my article: Apache Pinot and its various types of indexes<\/a>..<\/p>\n <\/p>","protected":false},"excerpt":{"rendered":" I’ve been wanting to test Apache Pinot for a very long time and I finally took the time to do it! First, a quick description of Pinot Pinot is a real-time distributed OLAP datastore, purpose-built to provide ultra low-latency analytics, even at extremely high throughput. It can ingest directly from streaming data sources or batch data sources. At the heart of the system is a columnar store, with several smart indexing and pre-aggregation techniques for low latency. Pinot was built…\ndocker run \\\n -p 9000:9000 \\\n apachepinot\/pinot:0.9.3 QuickStart \\\n -type batch\n<\/pre>\n
![\"\"](\"https:\/\/i0.wp.com\/loicmathieu.fr\/wordpress\/wp-content\/uploads\/pinot-cluster-view.png?resize=640%2C339&ssl=1\")
\ncount(*)<\/code> of the created baseballStats<\/strong> table, the query is executed almost immediately (a few milliseconds) but at the same time, there is only 97889 lines so it’s normal.<\/p>\n![\"\"](\"https:\/\/i0.wp.com\/loicmathieu.fr\/wordpress\/wp-content\/uploads\/pinot-first-query.png?resize=640%2C274&ssl=1\")
\nAnalysis of European carbon emissions<\/h2>\n
:<\/code>, the first column containing the NACE category it would be great to separate it into four columns: the labels are airpol,nace_r2,unit,geo\\time<\/strong>.<\/p>\n\n{\n \"schemaName\": \"greenhouseGazEmission\",\n \"dimensionFieldSpecs\": [\n {\n \"name\": \"airpol\",\n \"dataType\": \"STRING\"\n },\n {\n \"name\": \"nace_r2\",\n \"dataType\": \"STRING\"\n },\n {\n \"name\": \"unit\",\n \"dataType\": \"STRING\"\n },\n {\n \"name\": \"geo\",\n \"dataType\": \"STRING\"\n }\n ],\n \"metricFieldSpecs\": [\n {\n \"name\": \"2020\",\n \"dataType\": \"FLOAT\"\n },\n {\n \"name\": \"2019\",\n \"dataType\": \"FLOAT\"\n },\n [...] \/\/ repeate the same for all fields down to 1995\n ]\n}\n<\/pre>\n\n{\n \"tableName\": \"greenhouseGazEmission\",\n \"segmentsConfig\" : {\n \"replication\" : \"1\",\n \"schemaName\" : \"greenhouseGazEmission\"\n },\n \"tableIndexConfig\" : {\n \"invertedIndexColumns\" : [],\n \"loadMode\" : \"MMAP\"\n },\n \"tenants\" : {\n \"broker\":\"DefaultTenant\",\n \"server\":\"DefaultTenant\"\n },\n \"tableType\":\"OFFLINE\",\n \"metadata\": {}\n}\n<\/pre>\n\ndocker run --rm -ti \\\n --network=pinot_default \\\n -v ~\/dev\/pinot\/data:\/tmp\/pinot-quick-start \\\n --name pinot-batch-table-creation \\\n apachepinot\/pinot:0.9.3 AddTable \\\n -schemaFile \/tmp\/pinot-quick-start\/greenhousGazEmission-schema.json \\\n -tableConfigFile \/tmp\/pinot-quick-start\/greenhousGazEmission-table.json \\\n -controllerHost manual-pinot-controller \\\n -controllerPort 9000 -exec\n<\/pre>\n
:<\/code> when there is no data, to prepare it for ingestion into Pinot we will execute a sed<\/code> command to transform it into CSV (therefore replacing the tabulations by commas) and delete certain incorrect characters, this sed<\/code> command will also modify the line of field names which must then be returned to those expected by the schema.<\/p>\n\nsed 's\/\\t\/,\/g;s\/:\/\/g;s\/[pseb]\/\/g;s\/[[:blank:]]\/\/g' env_ac_ainah_r2.tsv > data\/env_ac_ainah_r2.csv\n<\/pre>\n
\nexecutionFrameworkSpec:\n name: 'standalone'\n segmentGenerationJobRunnerClassName: 'org.apache.pinot.plugin.ingestion.batch.standalone.SegmentGenerationJobRunner'\n segmentTarPushJobRunnerClassName: 'org.apache.pinot.plugin.ingestion.batch.standalone.SegmentTarPushJobRunner'\n segmentUriPushJobRunnerClassName: 'org.apache.pinot.plugin.ingestion.batch.standalone.SegmentUriPushJobRunner'\njobType: SegmentCreationAndTarPush\ninputDirURI: '\/tmp\/pinot-quick-start'\nincludeFileNamePattern: 'glob:**\/*.csv'\noutputDirURI: '\/tmp\/pinot-quick-start\/segments\/'\noverwriteOutput: true\npinotFSSpecs:\n - scheme: file\n className: org.apache.pinot.spi.filesystem.LocalPinotFS\nrecordReaderSpec:\n dataFormat: 'csv'\n className: 'org.apache.pinot.plugin.inputformat.csv.CSVRecordReader'\n configClassName: 'org.apache.pinot.plugin.inputformat.csv.CSVRecordReaderConfig'\ntableSpec:\n tableName: 'greenhouseGazEmission'\n schemaURI: 'http:\/\/pinot-controller:9000\/tables\/greenhouseGazEmission\/schema'\n tableConfigURI: 'http:\/\/pinot-controller:9000\/tables\/greenhouseGazEmission'\npinotClusterSpecs:\n - controllerURI: 'http:\/\/pinot-controller:9000'\n<\/pre>\n
jobType: SegmentCreationAndTarPush<\/code>: the job will create a table segment. A segment is a partition of the table data. If your data set is large, you will have to cut the CSV file to be able to launch several jobs and obtain several segments.<\/li>\n\ninputDirURI<\/code> and includeFileNamePattern<\/code> to define where to look up for the CSV(s) to load data from.<\/li>\n\nrecordReaderSpec<\/code> which defines the CSV data format.<\/li>\n\ntableSpec<\/code> which defines the target table specification, the one we defined earlier.<\/li>\n<\/ul>\n\ndocker run --rm -ti \\\n --network=pinot_default \\\n -v ~\/dev\/pinot\/data:\/tmp\/pinot-quick-start \\\n --name pinot-data-ingestion-job \\\n apachepinot\/pinot:0.9.3 LaunchDataIngestionJob \\\n -jobSpecFile \/tmp\/pinot-quick-start\/job-ingest.yml\n<\/pre>\n
select geo, sum(2019), sum(2020), sum(2021) from greenhouseGazEmission group by geo<\/code>.<\/p>\n![\"\"](\"https:\/\/i0.wp.com\/loicmathieu.fr\/wordpress\/wp-content\/uploads\/pinot-carbonn-footprint-query.png?resize=640%2C447&ssl=1\")
\nselect sum(2019), sum(2020), sum(2021) from greenhouseGazEmission where unit = 'G_HAB'<\/code>, it runs in 24ms, and we can see the following information in the result output:<\/p>\nOptimization of queries thanks to indexes<\/h2>\n
\n{\n \"tableName\": \"greenhouseGazEmission\",\n \"tableIndexConfig\" : {\n \"invertedIndexColumns\" : [\"unit\"],\n \"loadMode\" : \"MMAP\"\n },\n [...]\n}\n<\/pre>\n