codope commented on issue #10110:
URL: https://github.com/apache/hudi/issues/10110#issuecomment-1814463898
It should work with 1.0.0-beta1 with spark-sql. In the DDL page, I have
provided an example with `hour` function. You can replace it with
`from_unixtime`. This is what I tried and it works.
```
DROP TABLE IF EXISTS hudi_table;
CREATE TABLE hudi_table (
ts BIGINT,
uuid STRING,
rider STRING,
driver STRING,
fare DOUBLE,
city STRING
) USING HUDI
tblproperties (primaryKey = 'uuid')
PARTITIONED BY (city)
location 'file:///tmp/hudi_func_index';
-- disable small file handling so the each insert creates new file --
set hoodie.parquet.small.file.limit=0;
-- records with ts in [2023-09-18, 2023-09-24] --
INSERT INTO hudi_table VALUES
(1695159649,'334e26e9-8355-45cc-97c6-c31daf0df330','rider-A','driver-K',19.10,'san_francisco');
INSERT INTO hudi_table VALUES
(1695091554,'e96c4396-3fad-413a-a942-4cb36106d721','rider-C','driver-M',27.70
,'san_francisco');
INSERT INTO hudi_table VALUES
(1695046462,'9909a8b1-2d15-4d3d-8ec9-efc48c536a00','rider-D','driver-L',33.90
,'san_francisco');
INSERT INTO hudi_table VALUES
(1695332066,'1dced545-862b-4ceb-8b43-d2a568f6616b','rider-E','driver-O',93.50,'san_francisco');
INSERT INTO hudi_table VALUES
(1695516137,'e3cf430c-889d-4015-bc98-59bdce1e530c','rider-F','driver-P',34.15,'sao_paulo');
INSERT INTO hudi_table VALUES
(1695376420,'7a84095f-737f-40bc-b62f-6b69664712d2','rider-G','driver-Q',43.40
,'sao_paulo');
INSERT INTO hudi_table VALUES
(1695173887,'3eeb61f7-c2b0-4636-99bd-5d7a5a1d2c04','rider-I','driver-S',41.06
,'chennai');
INSERT INTO hudi_table VALUES
(1695115999,'c8abbe79-8d89-47ea-b4ce-4d224bae5bfa','rider-J','driver-T',17.85,'chennai');
SELECT city, fare, rider, driver, from_unixtime(ts, 'yyyy-MM-dd') as datestr
FROM hudi_table;
san_francisco 33.9 rider-D driver-L 2023-09-18
san_francisco 27.7 rider-C driver-M 2023-09-19
san_francisco 93.5 rider-E driver-O 2023-09-22
san_francisco 19.1 rider-A driver-K 2023-09-20
sao_paulo 43.4 rider-G driver-Q 2023-09-22
sao_paulo 34.15 rider-F driver-P 2023-09-24
chennai 17.85 rider-J driver-T 2023-09-19
chennai 41.06 rider-I driver-S 2023-09-20
SELECT city, fare, rider, driver FROM hudi_table WHERE city NOT IN
('chennai') AND from_unixtime(ts, 'yyyy-MM-dd') > '2023-09-20';
CREATE INDEX datestr ON hudi_table USING column_stats(ts)
options(func='from_unixtime', format='yyyy-MM-dd');
SELECT city, fare, rider, driver FROM hudi_table WHERE from_unixtime(ts,
'yyyy-MM-dd') > '2023-09-20';
san_francisco 93.5 rider-E driver-O
sao_paulo 43.4 rider-G driver-Q
sao_paulo 34.15 rider-F driver-P
Time taken: 0.428 seconds, Fetched 3 row(s)
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
