search nothing about '__HIVE_DEFAULT_PARTITION__'

@lingloong Welcome to the Cloudera Community!

To help you get the best possible solution, I have tagged our Hive experts @cravani @james_jones @ggangadharan  who may be able to assist you further

Please keep us updated on your post, and we hope you find a satisfactory solution to your query.

In Hive, each partition corresponds to a physical directory on the file system. Because an empty string ('') or a NULL value cannot be used as a directory name, Hive substitutes it with the default value from its configuration.

The setting hive.exec.default.partition.name provides this value, which is __HIVE_DEFAULT_PARTITION__.

When you query with a WHERE clause on a partitioned column, Hive performs partition pruning by filtering the directory names before it ever reads the data files. Therefore, to find data in the default partition, you must filter on the literal string name that Hive assigned to the directory.

This will work:
SELECT * FROM aaa WHERE data_dt = '__HIVE_DEFAULT_PARTITION__';

This won't work:
SELECT * FROM aaa WHERE data_dt IS NULL;
— This fails because the column's value is the string '__HIVE_DEFAULT_PARTITION__', not a true NULL.

Also, if you use Iceberg table format instead of default Hive format it will act more intuitively as a NULL without the placeholder partition name "__HIVE_DEFAULT_PARTITION__". 

So with Iceberg you can just do this:

SELECT * FROM aaa where data_dt IS NULL;

Depending on the environment/version you're running in, you can use iceberg this in any or all of: Impala, Spark3 and Hive.

In Hive , __HIVE_DEFAULT_PARTITION__ is a special value used internally to represent: 

NULL or EMPTY STRINGS in partition column values .

Since it's just a string literal in the metadata , you can access it just like any other string in a query. 

Example : 

SELECT * FROM your_table WHERE data_dt = '__HIVE_DEFAULT_PARTITION__';

We cannot do WHERE data_dt IS NULL — because NULLs are replaced with __HIVE_DEFAULT_PARTITION__ before being written

If you do WHERE length(data_dt) = 26 — it won’t match anything, because partition columns behave differently. 

@lingloong Has the reply helped resolve your issue? If so, please mark the appropriate reply as the solution, as it will make it easier for others to find the answer in the future. Thanks.