As shown on below image, I have two tables; - **Customer** : with about 4 million records; and 3 columns [Customer] : Customer ID [Date] : Creation date [Num_Days] : Number of working days to calculate the [next_wkday] in the query result. - **Calendar** : with all calendar days; and 3 columns [ref_date] : dates [civil_util] : when (1) -> workday; when (0) -> non working day [year] I need to get the **query_result** with the three columns of Customer Table and a calculated date [next_wkday], representing the number of working days [Num_Days] after each customer Creation date, jumping the zeros (*non working days*) in the [civil_util] column. I've created the query below to calculate the [next_wkday] using the Lead() function. But is not a solution because the **offset parameter** must be a constant, and we need to use the [Num_Days] value for each Creation Date: select *, Lead (to_date(ref_date),5) OVER (ORDER BY to_date(ref_date)) AS next_wkday, datediff(Lead (to_date(ref_date),5) OVER (ORDER BY to_date(ref_date)),ref_date) as days_diff from calendar where ref_date >= to_date(now()) and civil_util = 1 limit 1 Basicaly I need to solve two problems: 1 - Must be a query to perform the calculation Because I don't have the profile to create functions in the database. 2 - I need to design a query that achive the ***Query result***, shown in the image, that will able to join both, the Customer table and the calculation query. So, I need to find another solution. And that solution must work in two engines: 1. Impala version : impalad version 2.12.0-cdh5.16.2 2. Oracle 11g I need to reinforce, for performance puposes, that the Customer table has 4 billion records approximatly. Can anyone help please? My best regards

SQL/Impala I have a scenario where I need to join my input file with a mapping table which has map columns and derived columns. The join keys are dynamic and need to taken from a dynamic metadata table. Metadata table can be modified by user to either update or insert new keys. The join happens in multiple passes. Pass1 will have one set of columns as keys , Pass2 will have another set of columns as join keys. Pass2 derived values can overwrite Pass1 derived values. The derived columns are same through out. Example: Keys metadata table Mapping Table In Input File Join keys for Pass1 are A,B,C,D,E. Join will map values 10,20,30,40,50 from Input file to Values 10,20,30,40,50 in mapping table - DerivedValues1,2,3,4 will be derived. Join Keys for Pass2 are A,B,C,D - Join will map values 10,20,30,40 from Input File to values 10,20,30,40 in mapping table - DerivedValues1,2,3,4 will be derived and can overwrite values derived in Pass1 In Pass3 - Column A and Derived Value3 will be join key. Join will map values 15, Jasmine to mapping table - DerivedValues1,2,3,4 will be derived. Any idea how we could do this in SQL or Impala. Basically the platform is built in Big Data and we just need to write SQL like queries to implement this logic Appreciate any help. THanks Nitin

Suppose I am having a source Avro table having 10 columns and my target Avro table is having 12 columns, while inserting data into the target table I need to add null values to the extra 2 columns. But when I execute the below query it has thrown the exception > AnalysisException: Target table 'target_table' has more columns (8) than the SELECT / VALUES clause returns (7) insert overwrite table target_table select * from source_table; How to make advantage of Avro table automatic schema change detection here? **Note:** Suppose if I want to insert only 5 columns to the target and the rest should be default null. So how to achieve this?

I am trying to retrieve a list of transaction IDS that only contain at least 5 product IDS, the baskets could have many more product IDS but it must contain these 5 also. My table looks something like this: | transaction_id | trans_date | product_id | store_id | The table is all at product_id line level detail, nothing is rolled up. select * from Transactions where trans_date in ('2019-03-07','2019-03-06','2019-03-08','2019-03-09','2019-03-10') and product_id in ('000000000021003503','000000000060280873','000000000060281480','000000000060281642','000000000020059281') This is recovering all the transactions that contain these ids but I need it to be rolled up to a transaction level, so if the transaction_id contained these ids (at least) then its included. Hopefully this makes sense

I have a table having n number of records in impala. I need to find out how can I divide that table into two equal halves with 50 - 50 percent of records in each.