| e:[["$","script",null,{"type":"application/ld+json","dangerouslySetInnerHTML":{"__html":"{\"@context\":\"https://schema.org\",\"@type\":\"BlogPosting\",\"headline\":\"Apache Pinot™ 0.11 - Timestamp Indexes\",\"datePublished\":\"2022-11-22T00:00:00.000Z\",\"dateModified\":\"2022-11-22T00:00:00.000Z\",\"description\":\"Users write queries that use the datetrunc function to filter at a coarser grain of functionality. Unfortunately, this approach results in scanning data and time value conversion work that takes a long time at large data volumes. The timestamp index solves that problem!\",\"image\":\"/static/images/twitter-card.png\",\"url\":\"https://pinot.apache.org/blog/2022-11-22-Apache-Pinot-Timestamp-Indexes\",\"author\":[{\"@type\":\"Person\",\"name\":\"Mark Needham\"}]}"}}],["$","section",null,{"className":" px-5 pt-10 md:px-[13.313rem] md:py-16","children":[["$","$L10",null,{}],["$","article",null,{"className":"","children":["$","div",null,{"className":"mx-auto lg:flex","children":[["$","div",null,{"className":"lg:pr-12","children":[["$","header",null,{"className":"pt-6 md:pr-10","children":[["$","h1",null,{"className":"text-4xl font-semibold","children":"Apache Pinot™ 0.11 - Timestamp Indexes"}],["$","p",null,{"className":"pt-2 text-lg","children":["By: ","Mark Needham"]}],["$","p",null,{"className":"py-2 text-sm","children":["November 22nd, 2022"," • ","8 min read"]}]]}],["$","div",null,{"className":"flex flex-col lg:flex-row","children":["$","main",null,{"className":"","children":["$","div",null,{"className":"prose max-w-[45rem] pb-8 pt-10 dark:prose-invert","children":[" ",["$","$L11",null,{"src":"https://www.youtube.com/embed/DetGpHZuzDU","title":"YouTube video player"}],["$","p",null,{"children":["The recent Apache ",["$","a",null,{"target":"_blank","rel":"noopener noreferrer","href":"https://medium.com/apache-pinot-developer-blog/apache-pinot-0-11-released-d564684df5d4","children":"Pinot™ 0.11.0"}]," release has lots of goodies for you to play with. This is the third in a series of blog posts showing off some of the new features in this release."]}],["$","p",null,{"children":"Pinot introduced the TIMESTAMP data type in the 0.8 release, which stores the time in millisecond epoch long format internally. The community feedback has been that the queries they’re running against timestamp columns don’t need this low-level granularity."}],["$","p",null,{"children":"Instead, users write queries that use the datetrunc function to filter at a coarser grain of functionality. Unfortunately, this approach results in scanning data and time value conversion work that takes a long time at large data volumes."}],["$","p",null,{"children":["The ",["$","a",null,{"target":"_blank","rel":"noopener noreferrer","href":"https://docs.pinot.apache.org/basics/indexing/timestamp-index","children":"timestamp index"}]," solves that problem! In this blog post, we’ll use it to get an almost 5x query speed improvement on a relatively small dataset of only 7m rows."]}],["$","p",null,{"children":["$","img",null,{"alt":"Time in milliseconds with and without timestamp indexes bar chart","src":"https://www.datocms-assets.com/75153/1669133004-image1.png","title":"Time in milliseconds with and without timestamp indexes bar chart"}]}],["$","h2",null,{"id":"spinning-up-pinot","children":[["$","a",null,{"href":"#spinning-up-pinot","aria-hidden":"true","tabIndex":"-1","children":["$","span",null,{"className":"icon icon-link"}]}],"Spinning up Pinot"]}],["$","p",null,{"children":"We’re going to be using the Pinot Docker container, but first, we’re going to create a network, as we’ll need that later on:"}],["$","p",null,{"children":"docker network create timestamp_blog"}],["$","p",null,{"children":["We’re going to spin up the empty ",["$","a",null,{"target":"_blank","rel":"noopener noreferrer","href":"https://docs.pinot.apache.org/basics/getting-started/quick-start","children":"QuickStart"}]," in a container named pinot-timestamp-blog:"]}],["$","$L12",null,{"className":"language-bash","children":["$","code",null,{"className":"language-bash code-highlight","children":[["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token function","children":"docker"}]," run ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":[" ",["$","span",null,{"className":"token variable parameter","children":"-p"}]," ",["$","span",null,{"className":"token number","children":"8000"}],":8000 ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":[" ",["$","span",null,{"className":"token variable parameter","children":"-p"}]," ",["$","span",null,{"className":"token number","children":"9000"}],":9000 ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":[" ",["$","span",null,{"className":"token variable parameter","children":"--name"}]," pinot-timestamp-blog ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":[" ",["$","span",null,{"className":"token variable parameter","children":"--network"}]," timestamp_blog ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":[" apachepinot/pinot:0.11.0 ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":[" QuickStart ",["$","span",null,{"className":"token variable parameter","children":"-type"}]," EMPTY\n"]}]]}]}],["$","p",null,{"children":"Or if you’re on a Mac M1, change the name of the image to have the arm-64 suffix, like this:"}],["$","$L12",null,{"className":"language-bash","children":["$","code",null,{"className":"language-bash code-highlight","children":[["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token function","children":"docker"}]," run ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":[" ",["$","span",null,{"className":"token variable parameter","children":"-p"}]," ",["$","span",null,{"className":"token number","children":"8000"}],":8000 ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":[" ",["$","span",null,{"className":"token variable parameter","children":"-p"}]," ",["$","span",null,{"className":"token number","children":"9000"}],":9000 ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":[" ",["$","span",null,{"className":"token variable parameter","children":"--network"}]," timestamp_blog ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":[" ",["$","span",null,{"className":"token variable parameter","children":"--name"}]," pinot-timestamp-blog ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":[" apachepinot/pinot:0.11.0-arm64 ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":[" QuickStart ",["$","span",null,{"className":"token variable parameter","children":"-type"}]," EMPTY\n"]}]]}]}],["$","p",null,{"children":["Once that’s up and running, we’ll be able to access the Pinot Data Explorer at ",["$","a",null,{"target":"_blank","rel":"noopener noreferrer","href":"http://localhost:9000/","children":"http://localhost:9000"}],", but at the moment, we don’t have any data to play with."]}],["$","h2",null,{"id":"importing-chicago-crime-dataset","children":[["$","a",null,{"href":"#importing-chicago-crime-dataset","aria-hidden":"true","tabIndex":"-1","children":["$","span",null,{"className":"icon icon-link"}]}],"Importing Chicago Crime Dataset"]}],["$","p",null,{"children":["The ",["$","a",null,{"target":"_blank","rel":"noopener noreferrer","href":"https://startree.ai/blog/analyzing-chicago-crimes-with-apache-pinot-and-streamlit","children":"Chicago Crime dataset"}]," is a small to medium-sized dataset with 7 million records representing reported crimes in the City of Chicago from 2001 until today."]}],["$","p",null,{"children":"It contains details of the type of crime, where it was committed, whether an arrest was recorded, which beat it occurred on, and more."}],["$","p",null,{"children":"Each of the crimes has an associated timestamp, which makes it a good dataset to demonstrate timestamp indexes."}],["$","p",null,{"children":["You can find the code used in this blog post in the ",["$","a",null,{"target":"_blank","rel":"noopener noreferrer","href":"https://github.com/startreedata/pinot-recipes/tree/main/recipes/analyzing-chicago-crimes","children":"Analyzing Chicago Crimes"}]," recipe section of ",["$","a",null,{"target":"_blank","rel":"noopener noreferrer","href":"https://github.com/startreedata/pinot-recipes","children":"Pinot Recipes GitHub repository"}],". From here on, I’m assuming that you’ve downloaded this repository and are in the recipes/analyzing-chicago-crimes directory."]}],["$","p",null,{"children":"We’re going to create a schema and table named crimes by running the following command:"}],["$","$L12",null,{"className":"language-bash","children":["$","code",null,{"className":"language-bash code-highlight","children":[["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token function","children":"docker"}]," run ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":[" ",["$","span",null,{"className":"token variable parameter","children":"--network"}]," timestamp_blog ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":[" ",["$","span",null,{"className":"token variable parameter","children":"-v"}]," ",["$","span",null,{"className":"token constant environment","children":"$$PWD"}],"/config:/config ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":[" apachepinot/pinot:0.11.0-arm64 AddTable ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":[" ",["$","span",null,{"className":"token variable parameter","children":"-schemaFile"}]," /config/schema.json ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":[" ",["$","span",null,{"className":"token variable parameter","children":"-tableConfigFile"}]," /config/table.json ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":[" ",["$","span",null,{"className":"token variable parameter","children":"-controllerHost"}]," pinot-timestamp-blog ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":[" ",["$","span",null,{"className":"token variable parameter","children":"-exec"}],"\n"]}],["$","span",null,{"className":"code-line","children":"\n"}]]}]}],["$","p",null,{"children":"We should see the following output:"}],["$","$L12",null,{"className":"language-bash","children":["$","code",null,{"className":"language-bash code-highlight","children":["$","span",null,{"className":"code-line","children":[" ",["$","span",null,{"className":"token number","children":"2022"}],"/11/03 ",["$","span",null,{"className":"token number","children":"13"}],":07:57.169 INFO ",["$","span",null,{"className":"token punctuation","children":"\\"}],["$","span",null,{"className":"token punctuation","children":"["}],"AddTableCommand",["$","span",null,{"className":"token punctuation","children":"\\"}],["$","span",null,{"className":"token punctuation","children":"]"}]," ",["$","span",null,{"className":"token punctuation","children":"\\"}],["$","span",null,{"className":"token punctuation","children":"["}],"main",["$","span",null,{"className":"token punctuation","children":"\\"}],["$","span",null,{"className":"token punctuation","children":"]"}]," ",["$","span",null,{"className":"token punctuation","children":"{"}],["$","span",null,{"className":"token string","children":"\"unrecognizedProperties\""}],":",["$","span",null,{"className":"token punctuation","children":"{"}],["$","span",null,{"className":"token punctuation","children":"}"}],",",["$","span",null,{"className":"token string","children":"\"status\""}],["$","span",null,{"className":"token builtin class-name","children":":"}],["$","span",null,{"className":"token string","children":"\"TableConfigs crimes successfully added\""}],["$","span",null,{"className":"token punctuation","children":"}"}],"\n"]}]}]}],["$","p",null,{"children":"A screenshot of the schema is shown below:"}],["$","p",null,{"children":["$","img",null,{"alt":"Chicago crime dataset table schema","src":"https://www.datocms-assets.com/75153/1669132979-image3.png","title":"Chicago crime dataset table schema"}]}],["$","p",null,{"children":["We won’t go through the table config and schema files in this blog post because we did that in the last post, but you can find them in the ",["$","a",null,{"target":"_blank","rel":"noopener noreferrer","href":"https://github.com/startreedata/pinot-recipes/tree/main/recipes/analyzing-chicago-crimes/config","children":"config"}]," directory on GitHub."]}],["$","p",null,{"children":"Now, let’s import the dataset."}],["$","$L12",null,{"className":"language-bash","children":["$","code",null,{"className":"language-bash code-highlight","children":[["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token function","children":"docker"}]," run ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":[" ",["$","span",null,{"className":"token variable parameter","children":"--network"}]," timestamp_blog ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":[" ",["$","span",null,{"className":"token variable parameter","children":"-v"}]," ",["$","span",null,{"className":"token constant environment","children":"$$PWD"}],"/config:/config ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":[" ",["$","span",null,{"className":"token variable parameter","children":"-v"}]," ",["$","span",null,{"className":"token constant environment","children":"$$PWD"}],"/data:/data ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":[" apachepinot/pinot:0.11.0-arm64 LaunchDataIngestionJob ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":[" ",["$","span",null,{"className":"token variable parameter","children":"-jobSpecFile"}]," /config/job-spec.yml ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":[" ",["$","span",null,{"className":"token variable parameter","children":"-values"}]," ",["$","span",null,{"className":"token variable assign-left","children":"controllerHost"}],["$","span",null,{"className":"token operator","children":"="}],"pinot-timestamp-blog\n"]}]]}]}],["$","p",null,{"children":"It will take a few minutes to load, but once that command has finished, we’re ready to query the crimes table."}],["$","h2",null,{"id":"querying-crimes-by-date","children":[["$","a",null,{"href":"#querying-crimes-by-date","aria-hidden":"true","tabIndex":"-1","children":["$","span",null,{"className":"icon icon-link"}]}],"Querying crimes by date"]}],["$","p",null,{"children":"The following query finds the number of crimes that happened after 16th January 2017, grouped by week of the year, with the most crime-filled weeks shown first:"}],["$","$L12",null,{"className":"language-sql","children":["$","code",null,{"className":"code-highlight language-sql","children":[["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token keyword","children":"select"}]," datetrunc",["$","span",null,{"className":"token punctuation","children":"("}],["$","span",null,{"className":"token string","children":"'WEEK'"}],["$","span",null,{"className":"token punctuation","children":","}]," DateEpoch",["$","span",null,{"className":"token punctuation","children":")"}]," ",["$","span",null,{"className":"token keyword","children":"as"}]," tsWeek",["$","span",null,{"className":"token punctuation","children":","}]," ",["$","span",null,{"className":"token function","children":"count"}],["$","span",null,{"className":"token punctuation","children":"("}],["$","span",null,{"className":"token operator","children":"*"}],["$","span",null,{"className":"token punctuation","children":")"}],"\n"]}],["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token keyword","children":"from"}]," crimes\n"]}],["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token keyword","children":"WHERE"}]," tsWeek ",["$","span",null,{"className":"token operator","children":">"}]," fromDateTime",["$","span",null,{"className":"token punctuation","children":"("}],["$","span",null,{"className":"token string","children":"'2017-01-16'"}],["$","span",null,{"className":"token punctuation","children":","}]," ",["$","span",null,{"className":"token string","children":"'yyyy-MM-dd'"}],["$","span",null,{"className":"token punctuation","children":")"}],"\n"]}],["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token keyword","children":"group"}]," ",["$","span",null,{"className":"token keyword","children":"by"}]," tsWeek\n"]}],["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token keyword","children":"order"}]," ",["$","span",null,{"className":"token keyword","children":"by"}]," ",["$","span",null,{"className":"token function","children":"count"}],["$","span",null,{"className":"token punctuation","children":"("}],["$","span",null,{"className":"token operator","children":"*"}],["$","span",null,{"className":"token punctuation","children":")"}]," ",["$","span",null,{"className":"token keyword","children":"DESC"}],"\n"]}],["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token keyword","children":"limit"}]," ",["$","span",null,{"className":"token number","children":"10"}],"\n"]}]]}]}],["$","p",null,{"children":"If we run that query, we’ll see the following results:"}],["$","p",null,{"children":["$","img",null,{"alt":"Chicago crime dataset query result","src":"https://www.datocms-assets.com/75153/1669133027-image6.png","title":"Chicago crime dataset query result"}]}],["$","p",null,{"children":"And, if we look above the query result, there’s metadata about the query, including the time that it took to run."}],["$","p",null,{"children":["$","img",null,{"alt":"Chicago crime dataset metadata about the query, including the time that it took to run","src":"https://www.datocms-assets.com/75153/1669133059-image5.png","title":"Chicago crime dataset metadata about the query, including the time that it took to run"}]}],["$","p",null,{"children":"The query took 141 ms to execute, so that’s our baseline."}],["$","h2",null,{"id":"adding-the-timestamp-index","children":[["$","a",null,{"href":"#adding-the-timestamp-index","aria-hidden":"true","tabIndex":"-1","children":["$","span",null,{"className":"icon icon-link"}]}],"Adding the timestamp index"]}],["$","p",null,{"children":"We could add a timestamp index directly to this table and then compare query performance, but to make it easier to do comparisons, we’re going to create an identical table with the timestamp index applied."}],["$","p",null,{"children":["The full table config is available in the ",["$","a",null,{"target":"_blank","rel":"noopener noreferrer","href":"https://github.com/startreedata/pinot-recipes/blob/main/recipes/analyzing-chicago-crimes/config/table-index.json","children":"config/table-index.json"}]," file, and the main change is that we’ve added the following section to add a timestamp index on the DateEpoch column:"]}],["$","$L12",null,{"className":"language-json","children":["$","code",null,{"className":"code-highlight language-json","children":[["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token property","children":"\"fieldConfigList\""}],["$","span",null,{"className":"token operator","children":":"}]," ",["$","span",null,{"className":"token punctuation","children":"["}],"\n"]}],["$","span",null,{"className":"code-line","children":[" ",["$","span",null,{"className":"token punctuation","children":"{"}],"\n"]}],["$","span",null,{"className":"code-line","children":[" ",["$","span",null,{"className":"token property","children":"\"name\""}],["$","span",null,{"className":"token operator","children":":"}]," ",["$","span",null,{"className":"token string","children":"\"DateEpoch\""}],["$","span",null,{"className":"token punctuation","children":","}],"\n"]}],["$","span",null,{"className":"code-line","children":[" ",["$","span",null,{"className":"token property","children":"\"encodingType\""}],["$","span",null,{"className":"token operator","children":":"}]," ",["$","span",null,{"className":"token string","children":"\"DICTIONARY\""}],["$","span",null,{"className":"token punctuation","children":","}],"\n"]}],["$","span",null,{"className":"code-line","children":[" ",["$","span",null,{"className":"token property","children":"\"indexTypes\""}],["$","span",null,{"className":"token operator","children":":"}]," ",["$","span",null,{"className":"token punctuation","children":"["}],["$","span",null,{"className":"token string","children":"\"TIMESTAMP\""}],["$","span",null,{"className":"token punctuation","children":"]"}],["$","span",null,{"className":"token punctuation","children":","}],"\n"]}],["$","span",null,{"className":"code-line","children":[" ",["$","span",null,{"className":"token property","children":"\"timestampConfig\""}],["$","span",null,{"className":"token operator","children":":"}]," ",["$","span",null,{"className":"token punctuation","children":"{"}],"\n"]}],["$","span",null,{"className":"code-line","children":[" ",["$","span",null,{"className":"token property","children":"\"granularities\""}],["$","span",null,{"className":"token operator","children":":"}]," ",["$","span",null,{"className":"token punctuation","children":"["}],"\n"]}],["$","span",null,{"className":"code-line","children":[" ",["$","span",null,{"className":"token string","children":"\"DAY\""}],["$","span",null,{"className":"token punctuation","children":","}],"\n"]}],["$","span",null,{"className":"code-line","children":[" ",["$","span",null,{"className":"token string","children":"\"WEEK\""}],["$","span",null,{"className":"token punctuation","children":","}],"\n"]}],["$","span",null,{"className":"code-line","children":[" ",["$","span",null,{"className":"token string","children":"\"MONTH\""}],"\n"]}],["$","span",null,{"className":"code-line","children":[" ",["$","span",null,{"className":"token punctuation","children":"]"}],"\n"]}],["$","span",null,{"className":"code-line","children":[" ",["$","span",null,{"className":"token punctuation","children":"}"}],"\n"]}],["$","span",null,{"className":"code-line","children":[" ",["$","span",null,{"className":"token punctuation","children":"}"}],"\n"]}],["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token punctuation","children":"]"}],["$","span",null,{"className":"token punctuation","children":","}],"\n"]}]]}]}],["$","p",null,{"children":[["$","em",null,{"children":"encodingType"}]," will always be ‘DICTIONARY’ and ",["$","em",null,{"children":"indexTypes"}]," must contain ‘TIMESTAMP’. We should specify granularities based on our query patterns."]}],["$","p",null,{"children":["As a rule of thumb, work out which values you most commonly pass as the first argument to the ",["$","a",null,{"target":"_blank","rel":"noopener noreferrer","href":"https://docs.pinot.apache.org/configuration-reference/functions/datetrunc","children":"datetrunc function"}]," in your queries and include those values."]}],["$","p",null,{"children":["The full list of valid granularities is: ",["$","em",null,{"children":"millisecond"}],", ",["$","em",null,{"children":"second"}],", ",["$","em",null,{"children":"minute"}],", ",["$","em",null,{"children":"hour"}],", ",["$","em",null,{"children":"day"}],", ",["$","em",null,{"children":"week"}],", ",["$","em",null,{"children":"month"}],", ",["$","em",null,{"children":"quarter"}],", and ",["$","em",null,{"children":"year"}],"."]}],["$","p",null,{"children":"Our new table is called crimes_indexed, and we’re also going to create a new schema with all the same columns called crimes_indexed, as Pinot requires the table and schema names to match."}],["$","p",null,{"children":"We can create the schema and table by running the following command:"}],["$","$L12",null,{"className":"language-bash","children":["$","code",null,{"className":"language-bash code-highlight","children":[["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token function","children":"docker"}]," run ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":[" ",["$","span",null,{"className":"token variable parameter","children":"--network"}]," timestamp_blog ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":[" ",["$","span",null,{"className":"token variable parameter","children":"-v"}]," ",["$","span",null,{"className":"token constant environment","children":"$$PWD"}],"/config:/config ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":[" apachepinot/pinot:0.11.0-arm64 AddTable ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":[" ",["$","span",null,{"className":"token variable parameter","children":"-schemaFile"}]," /config/schema-index.json ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":[" ",["$","span",null,{"className":"token variable parameter","children":"-tableConfigFile"}]," /config/table-index.json ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":[" ",["$","span",null,{"className":"token variable parameter","children":"-controllerHost"}]," pinot-timestamp-blog ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":[" ",["$","span",null,{"className":"token variable parameter","children":"-exec"}],"\n"]}]]}]}],["$","p",null,{"children":"We’ll populate that table by copying the segment that we created earlier for the crimes table."}],["$","$L12",null,{"className":"language-bash","children":["$","code",null,{"className":"language-bash code-highlight","children":[["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token function","children":"docker"}]," run ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":[" ",["$","span",null,{"className":"token variable parameter","children":"--network"}]," timestamp_blog ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":[" ",["$","span",null,{"className":"token variable parameter","children":"-v"}]," ",["$","span",null,{"className":"token constant environment","children":"$$PWD"}],"/config:/config ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":[" ",["$","span",null,{"className":"token variable parameter","children":"-v"}]," ",["$","span",null,{"className":"token constant environment","children":"$$PWD"}],"/data:/data ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":[" apachepinot/pinot:0.11.0-arm64 LaunchDataIngestionJob ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":[" ",["$","span",null,{"className":"token variable parameter","children":"-jobSpecFile"}]," /config/job-spec-download.yml ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":[" ",["$","span",null,{"className":"token variable parameter","children":"-values"}]," ",["$","span",null,{"className":"token variable assign-left","children":"controllerHost"}],["$","span",null,{"className":"token operator","children":"="}],"pinot-timestamp-blog\n"]}]]}]}],["$","p",null,{"children":["If you’re curious how that job spec works, I ",["$","a",null,{"target":"_blank","rel":"noopener noreferrer","href":"https://www.markhneedham.com/blog/2021/12/06/apache-pinot-copy-segment-new-table/","children":"wrote a blog post explaining it in a bit more detail"}],"."]}],["$","p",null,{"children":"Once the Pinot Server has downloaded this segment, it will apply the timestamp index to the DateEpoch column."}],["$","p",null,{"children":"For the curious, we can see this happening in the log files by connecting to the Pinot container and running the following grep command:"}],["$","$L12",null,{"className":"language-bash","children":["$","code",null,{"className":"language-bash code-highlight","children":[["$","span",null,{"className":"code-line","children":["docker ",["$","span",null,{"className":"token builtin class-name","children":"exec"}]," ",["$","span",null,{"className":"token variable parameter","children":"-iti"}]," pinot-timestamp-blog ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":[" ",["$","span",null,{"className":"token function","children":"grep"}]," ",["$","span",null,{"className":"token variable parameter","children":"-rni"}]," ",["$","span",null,{"className":"token variable parameter","children":"-A10"}]," ",["$","span",null,{"className":"token string","children":"\"Successfully downloaded segment:.*crimes_indexed_OFFLINE.*\""}]," ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":" logs/pinot-all.log\n"}]]}]}],["$","p",null,{"children":"We’ll see something like the following (tidied up for brevity):"}],["$","$L12",null,{"className":"language-log","children":["$","code",null,{"className":"code-highlight language-log","children":[["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token punctuation","children":"["}],"BaseTableDataManager",["$","span",null,{"className":"token punctuation","children":"]"}]," ",["$","span",null,{"className":"token property","children":"Successfully downloaded segment:"}]," ",["$","span",null,{"className":"token property","children":"crimes_OFFLINE_0 of table:"}]," ",["$","span",null,{"className":"token property","children":"crimes_indexed_OFFLINE to index dir:"}]," ",["$","span",null,{"className":"token string file-path","children":"/tmp/1667490598253/quickstart/PinotServerDataDir0/crimes_indexed_OFFLINE/crimes_OFFLINE_0"}],"\n"]}],["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token punctuation","children":"["}],"V3DefaultColumnHandler",["$","span",null,{"className":"token punctuation","children":"]"}]," ",["$","span",null,{"className":"token property","children":"Starting default column action:"}]," ",["$","span",null,{"className":"token property","children":"ADD_DATE_TIME on column:"}]," ",["$","span",null,{"className":"token operator","children":"$$"}],"DateEpoch",["$","span",null,{"className":"token operator","children":"$$"}],"DAY\n"]}],["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token punctuation","children":"["}],"SegmentDictionaryCreator",["$","span",null,{"className":"token punctuation","children":"]"}]," ",["$","span",null,{"className":"token property","children":"Created dictionary for LONG column:"}]," ",["$","span",null,{"className":"token operator","children":"$$"}],"DateEpoch",["$","span",null,{"className":"token operator","children":"$$"}],"DAY with cardinality",["$","span",null,{"className":"token operator","children":":"}]," ",["$","span",null,{"className":"token number","children":"7969"}],["$","span",null,{"className":"token punctuation","children":","}]," range",["$","span",null,{"className":"token operator","children":":"}]," ",["$","span",null,{"className":"token number","children":"978307200000"}]," to ",["$","span",null,{"className":"token number","children":"1666742400000"}],"\n"]}],["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token punctuation","children":"["}],"V3DefaultColumnHandler",["$","span",null,{"className":"token punctuation","children":"]"}]," ",["$","span",null,{"className":"token property","children":"Starting default column action:"}]," ",["$","span",null,{"className":"token property","children":"ADD_DATE_TIME on column:"}]," ",["$","span",null,{"className":"token operator","children":"$$"}],"DateEpoch",["$","span",null,{"className":"token operator","children":"$$"}],"WEEK\n"]}],["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token punctuation","children":"["}],"SegmentDictionaryCreator",["$","span",null,{"className":"token punctuation","children":"]"}]," ",["$","span",null,{"className":"token property","children":"Created dictionary for LONG column:"}]," ",["$","span",null,{"className":"token operator","children":"$$"}],"DateEpoch",["$","span",null,{"className":"token operator","children":"$$"}],"WEEK with cardinality",["$","span",null,{"className":"token operator","children":":"}]," ",["$","span",null,{"className":"token number","children":"1139"}],["$","span",null,{"className":"token punctuation","children":","}]," range",["$","span",null,{"className":"token operator","children":":"}]," ",["$","span",null,{"className":"token number","children":"978307200000"}]," to ",["$","span",null,{"className":"token number","children":"1666569600000"}],"\n"]}],["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token punctuation","children":"["}],"V3DefaultColumnHandler",["$","span",null,{"className":"token punctuation","children":"]"}]," ",["$","span",null,{"className":"token property","children":"Starting default column action:"}]," ",["$","span",null,{"className":"token property","children":"ADD_DATE_TIME on column:"}]," ",["$","span",null,{"className":"token operator","children":"$$"}],"DateEpoch",["$","span",null,{"className":"token operator","children":"$$"}],"MONTH\n"]}],["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token punctuation","children":"["}],"SegmentDictionaryCreator",["$","span",null,{"className":"token punctuation","children":"]"}]," ",["$","span",null,{"className":"token property","children":"Created dictionary for LONG column:"}]," ",["$","span",null,{"className":"token operator","children":"$$"}],"DateEpoch",["$","span",null,{"className":"token operator","children":"$$"}],"MONTH with cardinality",["$","span",null,{"className":"token operator","children":":"}]," ",["$","span",null,{"className":"token number","children":"262"}],["$","span",null,{"className":"token punctuation","children":","}]," range",["$","span",null,{"className":"token operator","children":":"}]," ",["$","span",null,{"className":"token number","children":"978307200000"}]," to ",["$","span",null,{"className":"token number","children":"1664582400000"}],"\n"]}],["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token punctuation","children":"["}],"RangeIndexHandler",["$","span",null,{"className":"token punctuation","children":"]"}]," ",["$","span",null,{"className":"token property","children":"Creating new range index for segment:"}]," crimes_OFFLINE_0",["$","span",null,{"className":"token punctuation","children":","}]," column",["$","span",null,{"className":"token operator","children":":"}]," ",["$","span",null,{"className":"token operator","children":"$$"}],"DateEpoch",["$","span",null,{"className":"token operator","children":"$$"}],"DAY\n"]}],["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token punctuation","children":"["}],"RangeIndexHandler",["$","span",null,{"className":"token punctuation","children":"]"}]," ",["$","span",null,{"className":"token property","children":"Created range index for segment:"}]," crimes_OFFLINE_0",["$","span",null,{"className":"token punctuation","children":","}]," column",["$","span",null,{"className":"token operator","children":":"}]," ",["$","span",null,{"className":"token operator","children":"$$"}],"DateEpoch",["$","span",null,{"className":"token operator","children":"$$"}],"DAY\n"]}],["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token punctuation","children":"["}],"RangeIndexHandler",["$","span",null,{"className":"token punctuation","children":"]"}]," ",["$","span",null,{"className":"token property","children":"Creating new range index for segment:"}]," crimes_OFFLINE_0",["$","span",null,{"className":"token punctuation","children":","}]," column",["$","span",null,{"className":"token operator","children":":"}]," ",["$","span",null,{"className":"token operator","children":"$$"}],"DateEpoch",["$","span",null,{"className":"token operator","children":"$$"}],"WEEK\n"]}],["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token punctuation","children":"["}],"RangeIndexHandler",["$","span",null,{"className":"token punctuation","children":"]"}]," ",["$","span",null,{"className":"token property","children":"Created range index for segment:"}]," crimes_OFFLINE_0",["$","span",null,{"className":"token punctuation","children":","}]," column",["$","span",null,{"className":"token operator","children":":"}]," ",["$","span",null,{"className":"token operator","children":"$$"}],"DateEpoch",["$","span",null,{"className":"token operator","children":"$$"}],"WEEK\n"]}],["$","span",null,{"className":"code-line","children":"\n"}]]}]}],["$","h2",null,{"id":"what-does-a-timestamp-index-do","children":[["$","a",null,{"href":"#what-does-a-timestamp-index-do","aria-hidden":"true","tabIndex":"-1","children":["$","span",null,{"className":"icon icon-link"}]}],"What does a timestamp index do?"]}],["$","p",null,{"children":"So, the timestamp index has now been created, but what does it actually do?"}],["$","p",null,{"children":"When we add a timestamp index on a column, Pinot creates a derived column for each granularity and adds a range index for each new column."}],["$","p",null,{"children":["In our case, that means we’ll have these extra columns: ",["$","span",null,{"className":"math math-inline","children":["$","span",null,{"className":"katex","children":[["$","span",null,{"className":"katex-mathml","children":["$","math",null,{"xmlns":"http://www.w3.org/1998/Math/MathML","children":["$","semantics",null,{"children":[["$","mrow",null,{"children":[["$","mi",null,{"children":"D"}],["$","mi",null,{"children":"a"}],["$","mi",null,{"children":"t"}],["$","mi",null,{"children":"e"}],["$","mi",null,{"children":"E"}],["$","mi",null,{"children":"p"}],["$","mi",null,{"children":"o"}],["$","mi",null,{"children":"c"}],["$","mi",null,{"children":"h"}]]}],["$","annotation",null,{"encoding":"application/x-tex","children":"DateEpoch"}]]}]}]}],["$","span",null,{"className":"katex-html","aria-hidden":"true","children":["$","span",null,{"className":"base","children":[["$","span",null,{"className":"strut","style":{"height":".8889em","verticalAlign":"-.1944em"}}],["$","span",null,{"className":"mathnormal mord","style":{"marginRight":".02778em"},"children":"D"}],["$","span",null,{"className":"mathnormal mord","children":"a"}],["$","span",null,{"className":"mathnormal mord","children":"t"}],["$","span",null,{"className":"mathnormal mord","children":"e"}],["$","span",null,{"className":"mathnormal mord","children":"Ep"}],["$","span",null,{"className":"mathnormal mord","children":"oc"}],["$","span",null,{"className":"mathnormal mord","children":"h"}]]}]}]]}]}],"DAY, ",["$","span",null,{"className":"math math-inline","children":["$","span",null,{"className":"katex","children":[["$","span",null,{"className":"katex-mathml","children":["$","math",null,{"xmlns":"http://www.w3.org/1998/Math/MathML","children":["$","semantics",null,{"children":[["$","mrow",null,{"children":[["$","mi",null,{"children":"D"}],["$","mi",null,{"children":"a"}],["$","mi",null,{"children":"t"}],["$","mi",null,{"children":"e"}],["$","mi",null,{"children":"E"}],["$","mi",null,{"children":"p"}],["$","mi",null,{"children":"o"}],["$","mi",null,{"children":"c"}],["$","mi",null,{"children":"h"}]]}],["$","annotation",null,{"encoding":"application/x-tex","children":"DateEpoch"}]]}]}]}],["$","span",null,{"className":"katex-html","aria-hidden":"true","children":["$","span",null,{"className":"base","children":[["$","span",null,{"className":"strut","style":{"height":".8889em","verticalAlign":"-.1944em"}}],["$","span",null,{"className":"mathnormal mord","style":{"marginRight":".02778em"},"children":"D"}],["$","span",null,{"className":"mathnormal mord","children":"a"}],["$","span",null,{"className":"mathnormal mord","children":"t"}],["$","span",null,{"className":"mathnormal mord","children":"e"}],["$","span",null,{"className":"mathnormal mord","children":"Ep"}],["$","span",null,{"className":"mathnormal mord","children":"oc"}],["$","span",null,{"className":"mathnormal mord","children":"h"}]]}]}]]}]}],"WEEK, and ",["$","span",null,{"className":"math math-inline","children":["$","span",null,{"className":"katex","children":[["$","span",null,{"className":"katex-mathml","children":["$","math",null,{"xmlns":"http://www.w3.org/1998/Math/MathML","children":["$","semantics",null,{"children":[["$","mrow",null,{"children":[["$","mi",null,{"children":"D"}],["$","mi",null,{"children":"a"}],["$","mi",null,{"children":"t"}],["$","mi",null,{"children":"e"}],["$","mi",null,{"children":"E"}],["$","mi",null,{"children":"p"}],["$","mi",null,{"children":"o"}],["$","mi",null,{"children":"c"}],["$","mi",null,{"children":"h"}]]}],["$","annotation",null,{"encoding":"application/x-tex","children":"DateEpoch"}]]}]}]}],["$","span",null,{"className":"katex-html","aria-hidden":"true","children":["$","span",null,{"className":"base","children":[["$","span",null,{"className":"strut","style":{"height":".8889em","verticalAlign":"-.1944em"}}],["$","span",null,{"className":"mathnormal mord","style":{"marginRight":".02778em"},"children":"D"}],["$","span",null,{"className":"mathnormal mord","children":"a"}],["$","span",null,{"className":"mathnormal mord","children":"t"}],["$","span",null,{"className":"mathnormal mord","children":"e"}],["$","span",null,{"className":"mathnormal mord","children":"Ep"}],["$","span",null,{"className":"mathnormal mord","children":"oc"}],["$","span",null,{"className":"mathnormal mord","children":"h"}]]}]}]]}]}],"MONTH."]}],["$","p",null,{"children":["We can check if the extra columns and indexes have been added by navigating to the ",["$","a",null,{"target":"_blank","rel":"noopener noreferrer","href":"http://localhost:9000/#/tenants/table/crimes_indexed_OFFLINE/crimes_OFFLINE_0","children":"segment_page"}]," and typing ",["$","span",null,{"className":"math math-inline","children":["$","span",null,{"className":"katex","children":[["$","span",null,{"className":"katex-mathml","children":["$","math",null,{"xmlns":"http://www.w3.org/1998/Math/MathML","children":["$","semantics",null,{"children":[["$","mrow",null,{"children":[["$","mi",null,{"children":"D"}],["$","mi",null,{"children":"a"}],["$","mi",null,{"children":"t"}],["$","mi",null,{"children":"e"}]]}],["$","annotation",null,{"encoding":"application/x-tex","children":"Date"}]]}]}]}],["$","span",null,{"className":"katex-html","aria-hidden":"true","children":["$","span",null,{"className":"base","children":[["$","span",null,{"className":"strut","style":{"height":".6833em"}}],["$","span",null,{"className":"mathnormal mord","style":{"marginRight":".02778em"},"children":"D"}],["$","span",null,{"className":"mathnormal mord","children":"a"}],["$","span",null,{"className":"mathnormal mord","children":"t"}],["$","span",null,{"className":"mathnormal mord","children":"e"}]]}]}]]}]}],"Epoch in the search box. You should see the following:"]}],["$","p",null,{"children":["$","img",null,{"alt":"Apache Pinot timestamp index on a column","src":"https://www.datocms-assets.com/75153/1669133112-image2.png","title":"Apache Pinot timestamp index on a column"}]}],["$","p",null,{"children":"These columns will be assigned the following values:"}],["$","ul",null,{"children":[["$","li",null,{"children":[["$","span",null,{"className":"math math-inline","children":["$","span",null,{"className":"katex","children":[["$","span",null,{"className":"katex-mathml","children":["$","math",null,{"xmlns":"http://www.w3.org/1998/Math/MathML","children":["$","semantics",null,{"children":[["$","mrow",null,{"children":[["$","mi",null,{"children":"D"}],["$","mi",null,{"children":"a"}],["$","mi",null,{"children":"t"}],["$","mi",null,{"children":"e"}],["$","mi",null,{"children":"E"}],["$","mi",null,{"children":"p"}],["$","mi",null,{"children":"o"}],["$","mi",null,{"children":"c"}],["$","mi",null,{"children":"h"}]]}],["$","annotation",null,{"encoding":"application/x-tex","children":"DateEpoch"}]]}]}]}],["$","span",null,{"className":"katex-html","aria-hidden":"true","children":["$","span",null,{"className":"base","children":[["$","span",null,{"className":"strut","style":{"height":".8889em","verticalAlign":"-.1944em"}}],["$","span",null,{"className":"mathnormal mord","style":{"marginRight":".02778em"},"children":"D"}],["$","span",null,{"className":"mathnormal mord","children":"a"}],["$","span",null,{"className":"mathnormal mord","children":"t"}],["$","span",null,{"className":"mathnormal mord","children":"e"}],["$","span",null,{"className":"mathnormal mord","children":"Ep"}],["$","span",null,{"className":"mathnormal mord","children":"oc"}],["$","span",null,{"className":"mathnormal mord","children":"h"}]]}]}]]}]}],"DAY = dateTrunc(‘DAY’, DateEpoch)"]}],["$","li",null,{"children":[["$","span",null,{"className":"math math-inline","children":["$","span",null,{"className":"katex","children":[["$","span",null,{"className":"katex-mathml","children":["$","math",null,{"xmlns":"http://www.w3.org/1998/Math/MathML","children":["$","semantics",null,{"children":[["$","mrow",null,{"children":[["$","mi",null,{"children":"D"}],["$","mi",null,{"children":"a"}],["$","mi",null,{"children":"t"}],["$","mi",null,{"children":"e"}],["$","mi",null,{"children":"E"}],["$","mi",null,{"children":"p"}],["$","mi",null,{"children":"o"}],["$","mi",null,{"children":"c"}],["$","mi",null,{"children":"h"}]]}],["$","annotation",null,{"encoding":"application/x-tex","children":"DateEpoch"}]]}]}]}],["$","span",null,{"className":"katex-html","aria-hidden":"true","children":["$","span",null,{"className":"base","children":[["$","span",null,{"className":"strut","style":{"height":".8889em","verticalAlign":"-.1944em"}}],["$","span",null,{"className":"mathnormal mord","style":{"marginRight":".02778em"},"children":"D"}],["$","span",null,{"className":"mathnormal mord","children":"a"}],["$","span",null,{"className":"mathnormal mord","children":"t"}],["$","span",null,{"className":"mathnormal mord","children":"e"}],["$","span",null,{"className":"mathnormal mord","children":"Ep"}],["$","span",null,{"className":"mathnormal mord","children":"oc"}],["$","span",null,{"className":"mathnormal mord","children":"h"}]]}]}]]}]}],"WEEK = dateTrunc(‘WEEK’, DateEpoch)"]}],["$","li",null,{"children":[["$","span",null,{"className":"math math-inline","children":["$","span",null,{"className":"katex","children":[["$","span",null,{"className":"katex-mathml","children":["$","math",null,{"xmlns":"http://www.w3.org/1998/Math/MathML","children":["$","semantics",null,{"children":[["$","mrow",null,{"children":[["$","mi",null,{"children":"D"}],["$","mi",null,{"children":"a"}],["$","mi",null,{"children":"t"}],["$","mi",null,{"children":"e"}],["$","mi",null,{"children":"E"}],["$","mi",null,{"children":"p"}],["$","mi",null,{"children":"o"}],["$","mi",null,{"children":"c"}],["$","mi",null,{"children":"h"}]]}],["$","annotation",null,{"encoding":"application/x-tex","children":"DateEpoch"}]]}]}]}],["$","span",null,{"className":"katex-html","aria-hidden":"true","children":["$","span",null,{"className":"base","children":[["$","span",null,{"className":"strut","style":{"height":".8889em","verticalAlign":"-.1944em"}}],["$","span",null,{"className":"mathnormal mord","style":{"marginRight":".02778em"},"children":"D"}],["$","span",null,{"className":"mathnormal mord","children":"a"}],["$","span",null,{"className":"mathnormal mord","children":"t"}],["$","span",null,{"className":"mathnormal mord","children":"e"}],["$","span",null,{"className":"mathnormal mord","children":"Ep"}],["$","span",null,{"className":"mathnormal mord","children":"oc"}],["$","span",null,{"className":"mathnormal mord","children":"h"}]]}]}]]}]}],"MONTH = dateTrunc(‘MONTH’, DateEpoch)"]}]]}],["$","p",null,{"children":"Pinot will also rewrite any queries that use the dateTrunc function with DAY, WEEK, or MONTH and the DateEpoch field to use those new columns."}],["$","p",null,{"children":"This means that this query:"}],["$","$L12",null,{"className":"language-sql","children":["$","code",null,{"className":"code-highlight language-sql","children":[["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token keyword","children":"select"}]," datetrunc",["$","span",null,{"className":"token punctuation","children":"("}],["$","span",null,{"className":"token string","children":"'WEEK'"}],["$","span",null,{"className":"token punctuation","children":","}]," DateEpoch",["$","span",null,{"className":"token punctuation","children":")"}]," ",["$","span",null,{"className":"token keyword","children":"as"}]," tsWeek",["$","span",null,{"className":"token punctuation","children":","}]," ",["$","span",null,{"className":"token function","children":"count"}],["$","span",null,{"className":"token punctuation","children":"("}],["$","span",null,{"className":"token operator","children":"*"}],["$","span",null,{"className":"token punctuation","children":")"}],"\n"]}],["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token keyword","children":"from"}]," crimes_indexed\n"]}],["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token keyword","children":"GROUP"}]," ",["$","span",null,{"className":"token keyword","children":"BY"}]," tsWeek\n"]}],["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token keyword","children":"limit"}]," ",["$","span",null,{"className":"token number","children":"10"}],"\n"]}]]}]}],["$","p",null,{"children":"Would be rewritten as:"}],["$","$L12",null,{"className":"language-sql","children":["$","code",null,{"className":"code-highlight language-sql","children":[["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token keyword","children":"select"}]," $DateEpoch$WEEK ",["$","span",null,{"className":"token keyword","children":"as"}]," tsWeek",["$","span",null,{"className":"token punctuation","children":","}]," ",["$","span",null,{"className":"token function","children":"count"}],["$","span",null,{"className":"token punctuation","children":"("}],["$","span",null,{"className":"token operator","children":"*"}],["$","span",null,{"className":"token punctuation","children":")"}],"\n"]}],["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token keyword","children":"from"}]," crimes_indexed\n"]}],["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token keyword","children":"GROUP"}]," ",["$","span",null,{"className":"token keyword","children":"BY"}]," tsWeek\n"]}],["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token keyword","children":"limit"}]," ",["$","span",null,{"className":"token number","children":"10"}],"\n"]}]]}]}],["$","p",null,{"children":"And our query:"}],["$","$L12",null,{"className":"language-sql","children":["$","code",null,{"className":"code-highlight language-sql","children":[["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token keyword","children":"select"}]," datetrunc",["$","span",null,{"className":"token punctuation","children":"("}],["$","span",null,{"className":"token string","children":"'WEEK'"}],["$","span",null,{"className":"token punctuation","children":","}]," DateEpoch",["$","span",null,{"className":"token punctuation","children":")"}]," ",["$","span",null,{"className":"token keyword","children":"as"}]," tsWeek",["$","span",null,{"className":"token punctuation","children":","}]," ",["$","span",null,{"className":"token function","children":"count"}],["$","span",null,{"className":"token punctuation","children":"("}],["$","span",null,{"className":"token operator","children":"*"}],["$","span",null,{"className":"token punctuation","children":")"}],"\n"]}],["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token keyword","children":"from"}]," crimes\n"]}],["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token keyword","children":"WHERE"}]," tsWeek ",["$","span",null,{"className":"token operator","children":">"}]," fromDateTime",["$","span",null,{"className":"token punctuation","children":"("}],["$","span",null,{"className":"token string","children":"'2017-01-16'"}],["$","span",null,{"className":"token punctuation","children":","}]," ",["$","span",null,{"className":"token string","children":"'yyyy-MM-dd'"}],["$","span",null,{"className":"token punctuation","children":")"}],"\n"]}],["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token keyword","children":"group"}]," ",["$","span",null,{"className":"token keyword","children":"by"}]," tsWeek\n"]}],["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token keyword","children":"order"}]," ",["$","span",null,{"className":"token keyword","children":"by"}]," ",["$","span",null,{"className":"token function","children":"count"}],["$","span",null,{"className":"token punctuation","children":"("}],["$","span",null,{"className":"token operator","children":"*"}],["$","span",null,{"className":"token punctuation","children":")"}]," ",["$","span",null,{"className":"token keyword","children":"DESC"}],"\n"]}],["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token keyword","children":"limit"}]," ",["$","span",null,{"className":"token number","children":"10"}],"\n"]}]]}]}],["$","p",null,{"children":"Would be rewritten as:"}],["$","$L12",null,{"className":"language-sql","children":["$","code",null,{"className":"code-highlight language-sql","children":[["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token keyword","children":"select"}]," $DateEpoch$WEEK ",["$","span",null,{"className":"token keyword","children":"as"}]," tsWeek",["$","span",null,{"className":"token punctuation","children":","}]," ",["$","span",null,{"className":"token function","children":"count"}],["$","span",null,{"className":"token punctuation","children":"("}],["$","span",null,{"className":"token operator","children":"*"}],["$","span",null,{"className":"token punctuation","children":")"}],"\n"]}],["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token keyword","children":"from"}]," crimes\n"]}],["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token keyword","children":"WHERE"}]," tsWeek ",["$","span",null,{"className":"token operator","children":">"}]," fromDateTime",["$","span",null,{"className":"token punctuation","children":"("}],["$","span",null,{"className":"token string","children":"'2017-01-16'"}],["$","span",null,{"className":"token punctuation","children":","}]," ",["$","span",null,{"className":"token string","children":"'yyyy-MM-dd'"}],["$","span",null,{"className":"token punctuation","children":")"}],"\n"]}],["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token keyword","children":"group"}]," ",["$","span",null,{"className":"token keyword","children":"by"}]," tsWeek\n"]}],["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token keyword","children":"order"}]," ",["$","span",null,{"className":"token keyword","children":"by"}]," ",["$","span",null,{"className":"token function","children":"count"}],["$","span",null,{"className":"token punctuation","children":"("}],["$","span",null,{"className":"token operator","children":"*"}],["$","span",null,{"className":"token punctuation","children":")"}]," ",["$","span",null,{"className":"token keyword","children":"DESC"}],"\n"]}],["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token keyword","children":"limit"}]," ",["$","span",null,{"className":"token number","children":"10"}],"\n"]}]]}]}],["$","h2",null,{"id":"re-running-the-query","children":[["$","a",null,{"href":"#re-running-the-query","aria-hidden":"true","tabIndex":"-1","children":["$","span",null,{"className":"icon icon-link"}]}],"Re-running the query"]}],["$","p",null,{"children":["Let’s now run our initial query against the ",["$","em",null,{"children":"crimes_indexed"}]," table. We’ll get exactly the same results as before, but let’s take a look at the query stats:"]}],["$","p",null,{"children":["$","img",null,{"alt":"Chicago crime dataset updated query stats","src":"https://www.datocms-assets.com/75153/1669133083-image4.png","title":"Chicago crime dataset updated query stats"}]}],["$","p",null,{"children":"This time the query takes 36 milliseconds rather than 140 milliseconds. That’s an almost 5x improvement, thanks to the timestamp index."}],["$","h2",null,{"id":"summary","children":[["$","a",null,{"href":"#summary","aria-hidden":"true","tabIndex":"-1","children":["$","span",null,{"className":"icon icon-link"}]}],"Summary"]}],["$","p",null,{"children":"Hopefully, you’ll agree that timestamp indexes are pretty cool, and achieving a 5x query improvement without much work is always welcome!"}],["$","p",null,{"children":["If you’re using timestamps in your Pinot tables, be sure to try out this index and let us know how it goes on the ",["$","a",null,{"target":"_blank","rel":"noopener noreferrer","href":"https://stree.ai/slack","children":"StarTree Community Slack"}]," . We’re always happy to help out with any questions or problems you encounter."]}]]}]}]}]]}],["$","aside",null,{"className":"mt-10 hidden border-l-2 pl-5 lg:sticky lg:top-1 lg:block lg:h-full","children":["$","section",null,{"className":"sticky top-0 mb-4 w-[15.375rem]","children":[["$","div",null,{"className":"flex flex-col space-y-1.5 pb-3","children":["$","h3",null,{"className":"text-sm font-semibold leading-snug text-neutral-500 dark:text-neutral-100","children":"Table of Contents"}]}],["$","$L13",null,{"chapters":[{"value":"Spinning up Pinot","url":"#spinning-up-pinot","depth":2},{"value":"Importing Chicago Crime Dataset","url":"#importing-chicago-crime-dataset","depth":2},{"value":"Querying crimes by date","url":"#querying-crimes-by-date","depth":2},{"value":"Adding the timestamp index","url":"#adding-the-timestamp-index","depth":2},{"value":"What does a timestamp index do?","url":"#what-does-a-timestamp-index-do","depth":2},{"value":"Re-running the query","url":"#re-running-the-query","depth":2},{"value":"Summary","url":"#summary","depth":2}]}]]}]}]]}]}]]}]] |