I have a column stored in JSON that looks like column name: s2s_payload Values: I want to query exact values in the array rather than returning all values for a certain data type. I was using JSON_EXTRACT to get distinct counts. If I want to filter where “”eventtype””:””sea…
Tag: presto
how i can make left join work only when the number of records in the first table more or equal the number of records in the second table?
I have two tables and I want do left join between them, but I want the left join to happen only if the count of records that contain the same value of join column in the first table is more or equal the count of records that contain the same value of join column in the second table what I
SQL: Select the Running Total of Column C for pairwise combinations of two other columns A and B
I’m trying to query a table and calculate the running sum of a column’s values for pairwise combinations of two other columns. Specifically, given the following table: CREATE TABLE test ( bucket int(…
sql left email till @ symbol
i have a column called Email in my sql database so i need to left my column to get whats before the @ symbol
Creating bins in presto sql – programmatically
I am new to Presto SQL syntax and and wondering if a function exists that will bin rows into n bins in a certain range. For example, I have a a table with 1m different integers that range from 1 – 100. What can I do to create 20 bins between 1 and 100 (a bin for 1-5, 6-10, 11-15
How to fix ‘must be an aggregate expression or appear in GROUP BY clause’ with ‘as’ clause
I am trying to get the number of request by hour of my CloudFront distribution using athena query. I created cloudfront_logs table guided by this Link in my sample_db Below is the query I made to …
how to find Age from DOB in Athena?
Here is my query to find out age from the DOB column : I am getting following error: SYNTAX_ERROR: line 4:47: ‘-‘ cannot be applied to varchar, varchar Appreciate your help! Answer You should be able to solve this using function DATE_DIFF(). From the documentation: date_diff(unit, timestamp1, time…
Presto – hex string to int
I’m trying to convert hex string (starts with ‘0x’) to it’s integer value using presto. For example 0x100 to 256. My hex string is called msg_id. I tried to use this- But I run into a problem, because from_hex expect even number of hex digits (0100 instead of 100). I decided to try and…
How to convert a date format YYYY-MM-DD into integer YYYYMMDD in Presto/Hive?
How to CONVERT a date in format YYYY-MM-DD into integer YYYYMMDD in Presto/Hive? I am trying to convert the below list into YYYYMMDD integers WITH all_dates as (SELECT CAST(date_column AS …
Presto create table with ‘with’ queries
typically to create a table in Presto (from existing db tables), I do: But to make my code simple, I’ve broken out subqueries like this: Where do I put the create table statement here? The actual query is more complex than the above so I am trying to avoid having to put the subqueries within the main qu…