part-loc

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

part-loc [2015/09/23 20:27]
part-loc [2015/09/23 20:27] (current)
Line 1: Line 1:
 +====== PART_LOC Function ======
  
 +PART_LOC function returns the location of the specified table partition in HDFS or other storage.
 +
 +**Syntax**:
 +
 +<code language="​sql">​
 +PART_LOC([db_name.]table_name,​ part_col=filter,​ ... [, with_hostname]); ​
 +</​code>​
 +
 +**Parameters:​**
 +
 +| **Parameter** | **Type** | **Value** | **Description** |
 +| [dbname.]table_name | VARCHAR | Identifier, variable or expression | Table name |
 +| part_col=filter | | | One or more partition filters |
 +| with_hostname | INT | Variable or expression | 1 - return path with host name \\ 0 - without host name (default) |
 +
 +**Notes**:
 +
 +  * HPL/SQL uses the following Hive statement to get the partition information:​
 +
 +<code language="​sql">​
 +DESCRIBE EXTENDED db_name.tab_name PARTITION (part_col=filter,​ ...)
 +</​code>​
 +
 +**Return Value:**
 +
 +  * The partition directory
 +  * NULL if the table or partition do not exist
 +
 +**Return Type:**
 +
 +STRING (VARCHAR/​CHAR).
 +
 +**Example:​**
 +
 +Table //​db.orders//​ is partitioned by //country// and has the following partitions:
 +
 +| **Partition** | **Directory** |
 +| country='​US'​ | /​data/​db/​orders/​country=US |
 +| country='​UK'​ | /​data/​db/​orders/​country=UK |
 +| country='​DE'​ | /​data/​db/​orders/​country=DE |
 +| country='​BY'​ | /​data/​db/​orders/​country=BY |
 +
 +Get the location of country=US partition:
 +
 +<code language="​sql">​
 +PART_LOC(db.orders,​ country='​US',​ 1); 
 +
 +Result:
 +hdfs://​hostname:​8020/​data/​db/​orders/​country=US
 +</​code>​
 +
 +Return the path without host name:
 +
 +<code language="​sql">​
 +PART_LOC(db.orders,​ country='​US'​); ​
 +
 +Result:
 +/​data/​db/​orders/​country=US
 +</​code> ​
 +
 +**Compatibility**:​ HPL/SQL extension.
 +
 +**Version:​** HPL/SQL 0.1