You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@hawq.apache.org by yo...@apache.org on 2017/01/20 16:41:40 UTC
[1/7] incubator-hawq-docs git commit: HAWQ-1263 - clarifications to
ambari (inc subnav), hawq op env (closes #84)
Repository: incubator-hawq-docs
Updated Branches:
refs/heads/release/2.1.0.0-incubating 06d7cd275 -> c8cb30276
HAWQ-1263 - clarifications to ambari (inc subnav), hawq op env (closes #84)
Project: http://git-wip-us.apache.org/repos/asf/incubator-hawq-docs/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-hawq-docs/commit/2c026427
Tree: http://git-wip-us.apache.org/repos/asf/incubator-hawq-docs/tree/2c026427
Diff: http://git-wip-us.apache.org/repos/asf/incubator-hawq-docs/diff/2c026427
Branch: refs/heads/release/2.1.0.0-incubating
Commit: 2c0264272c05c23a327b2ef97e812a098eba551c
Parents: 06d7cd2
Author: Lisa Owen <lo...@pivotal.io>
Authored: Wed Jan 11 14:44:43 2017 -0800
Committer: David Yozie <yo...@apache.org>
Committed: Wed Jan 11 14:44:43 2017 -0800
----------------------------------------------------------------------
.../source/subnavs/apache-hawq-nav-210.erb | 10 ++--
markdown/admin/ambari-admin.html.md.erb | 2 +
markdown/admin/ambari-rest-api.html.md.erb | 50 ++++++++++----------
markdown/admin/setuphawqopenv.html.md.erb | 7 +--
4 files changed, 38 insertions(+), 31 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-hawq-docs/blob/2c026427/book/master_middleman/source/subnavs/apache-hawq-nav-210.erb
----------------------------------------------------------------------
diff --git a/book/master_middleman/source/subnavs/apache-hawq-nav-210.erb b/book/master_middleman/source/subnavs/apache-hawq-nav-210.erb
index 50ee5bc..1c7449f 100644
--- a/book/master_middleman/source/subnavs/apache-hawq-nav-210.erb
+++ b/book/master_middleman/source/subnavs/apache-hawq-nav-210.erb
@@ -79,11 +79,13 @@
<li>
<a href="/docs/userguide/2.1.0.0-incubating/admin/setuphawqopenv.html">Introducing the HAWQ Operating Environment</a>
</li>
- <li>
+ <li class="has_submenu">
<a href="/docs/userguide/2.1.0.0-incubating/admin/ambari-admin.html">Managing HAWQ Using Ambari</a>
- </li>
- <li>
- <a href="/docs/userguide/2.1.0.0-incubating/admin/ambari-rest-api.html">Using the Ambari REST API</a>
+ <ul>
+ <li>
+ <a href="/docs/userguide/2.1.0.0-incubating/admin/ambari-rest-api.html">Using the Ambari REST API</a>
+ </li>
+ </ul>
</li>
<li>
<a href="/docs/userguide/2.1.0.0-incubating/admin/startstop.html">Starting and Stopping HAWQ</a>
http://git-wip-us.apache.org/repos/asf/incubator-hawq-docs/blob/2c026427/markdown/admin/ambari-admin.html.md.erb
----------------------------------------------------------------------
diff --git a/markdown/admin/ambari-admin.html.md.erb b/markdown/admin/ambari-admin.html.md.erb
index a5b2169..d080b2e 100644
--- a/markdown/admin/ambari-admin.html.md.erb
+++ b/markdown/admin/ambari-admin.html.md.erb
@@ -437,3 +437,5 @@ To set the threshold for the unresponsive segments that will trigger an alert:
3. Click **Save** when done.
Alerts for **WARN**, and **CRITICAL** will be displayed when the number of unresponsive HAWQ segments in the cluster is greater than the specified percentage.
+## <a id="ambari-rest-api"></a>Using the Ambari REST API
+You can perform certain HAWQ cluster management activities from the command line using Ambari REST APIs. Refer to [Using the Ambari REST API](ambari-rest-api.html) for additional information.
http://git-wip-us.apache.org/repos/asf/incubator-hawq-docs/blob/2c026427/markdown/admin/ambari-rest-api.html.md.erb
----------------------------------------------------------------------
diff --git a/markdown/admin/ambari-rest-api.html.md.erb b/markdown/admin/ambari-rest-api.html.md.erb
index 2cc79e4..f029acb 100644
--- a/markdown/admin/ambari-rest-api.html.md.erb
+++ b/markdown/admin/ambari-rest-api.html.md.erb
@@ -30,7 +30,9 @@ The Ambari REST API supports standard HTTP request methods including:
- `GET` - read resource properties, metrics
- `POST` - create new resource
- `PUT` - update resource
-- `DELETE` - delete resource
+- `DELETE` - delete resource (use with care)
+
+**Note**: Be careful when using `DELETE` or `PUT` requests; typos or other incorrect usage may leave your cluster in an inoperable state.
URIs for Ambari REST API resources have the following structure:
@@ -88,16 +90,16 @@ Example: Testing Authentication
1. Set up some environment variables; replace the values with those appropriate for your operating environment. For example:
``` shell
- $ export AMBUSER=admin
- $ export AMBPASSWD=admin
- $ export AMBHOST=<ambari-server>
- $ export AMBPORT=8080
+ $ export AMBARI_USER=admin
+ $ export AMBARI_PASSWD=admin
+ $ export AMBARI_HOST=<ambari-server>
+ $ export AMBARI_PORT=8080
```
2. Submit a `curl` request to the Ambari server:
``` shell
- $ curl -u $AMBUSER:$AMBPASSWD http://$AMBHOST:$AMBPORT
+ $ curl -u $AMBARI_USER:$AMBARI_PASSWD http://$AMBARI_HOST:$AMBARI_PORT
```
If authentication succeeds, Apache license information is displayed.
@@ -111,19 +113,19 @@ Example: Testing Authentication
1. Set up an additional environment variables:
``` shell
- $ export AMBCREDS="$AMBUSER:$AMBPASSWD"
- $ export AMBURLBASE="http://${AMBHOST}:${AMBPORT}/api/v1/clusters"
+ $ export AMBARI_CREDS="$AMBARI_USER:$AMBARI_PASSWD"
+ $ export AMBARI_URLBASE="http://${AMBARI_HOST}:${AMBARI_PORT}/api/v1/clusters"
```
You will use these variables in upcoming examples to simplify `curl` calls.
-2. Use the Ambari REST API to determine the name of your HAWQ cluster; also set `$AMBURLBASE` to include the cluster name:
+2. Use the Ambari REST API to determine the name of your HAWQ cluster; also set `$AMBARI_URLBASE` to include the cluster name:
``` shell
- $ export CLUSTER_NAME="$(curl -u ${AMBCREDS} -i -H 'X-Requested-By:ambari' $AMBURLBASE | sed -n 's/.*"cluster_name" : "\([^\"]*\)".*/\1/p')"
+ $ export CLUSTER_NAME="$(curl -u ${AMBARI_CREDS} -i -H 'X-Requested-By:ambari' $AMBARI_URLBASE | sed -n 's/.*"cluster_name" : "\([^\"]*\)".*/\1/p')"
$ echo $CLUSTER_NAME
TestCluster
- $ export AMBURLBASE=$AMBURLBASE/$CLUSTER_NAME
+ $ export AMBARI_URLBASE=$AMBARI_URLBASE/$CLUSTER_NAME
```
### <a id="ambari-rest-ex-mgmt"></a>Examples: Managing the HAWQ and PXF Services
@@ -137,27 +139,27 @@ Refer to [API usage scenarios, troubleshooting, and other FAQs](https://cwiki.ap
| Task |Command |
|----------------------|------------------------|
-| View HAWQ service information. | `curl -u $AMBCREDS -X GET -H 'X-Requested-By:ambari' $AMBURLBASE/services/HAWQ` |
-| List all HAWQ components. | `curl -u $AMBCREDS -X GET -H 'X-Requested-By:ambari' $AMBURLBASE/services/HAWQ/components` |
-| View information about the HAWQ master. | `curl -u $AMBCREDS -X GET -H 'X-Requested-By:ambari' $AMBURLBASE/services/HAWQ/components/HAWQMASTER` |
-| View the `hawq-site` configuration settings. | `curl -u $AMBCREDS -X GET -H 'X-Requested-By:ambari' "$AMBURLBASE/configurations?type=hawq-site&tag=TOPOLOGY_RESOLVED"` |
-| View the initial `core-site` configuration settings. | `curl -u $AMBCREDS -X GET -H 'X-Requested-By:ambari' "$AMBURLBASE/configurations?type=core-site&tag=INITIAL"` |
-| View the `pxf-profiles` configuration file. | `curl -u $AMBCREDS -X GET -H 'X-Requested-By:ambari' "$AMBURLBASE/configurations?type=pxf-profiles&tag=INITIAL"` |
-| View all components on node. | `curl -u $AMBCREDS -i -X GET -H 'X-Requested-B:ambari' $AMBURLBASE/hosts/<hawq-node>` |
+| View HAWQ service information. | `curl -u $AMBARI_CREDS -X GET -H 'X-Requested-By:ambari' $AMBARI_URLBASE/services/HAWQ` |
+| List all HAWQ components. | `curl -u $AMBARI_CREDS -X GET -H 'X-Requested-By:ambari' $AMBARI_URLBASE/services/HAWQ/components` |
+| View information about the HAWQ master. | `curl -u $AMBARI_CREDS -X GET -H 'X-Requested-By:ambari' $AMBARI_URLBASE/services/HAWQ/components/HAWQMASTER` |
+| View the `hawq-site` configuration settings. | `curl -u $AMBARI_CREDS -X GET -H 'X-Requested-By:ambari' "$AMBARI_URLBASE/configurations?type=hawq-site&tag=TOPOLOGY_RESOLVED"` |
+| View the initial `core-site` configuration settings. | `curl -u $AMBARI_CREDS -X GET -H 'X-Requested-By:ambari' "$AMBARI_URLBASE/configurations?type=core-site&tag=INITIAL"` |
+| View the `pxf-profiles` configuration file. | `curl -u $AMBARI_CREDS -X GET -H 'X-Requested-By:ambari' "$AMBARI_URLBASE/configurations?type=pxf-profiles&tag=INITIAL"` |
+| View all components on node. | `curl -u $AMBARI_CREDS -i -X GET -H 'X-Requested-B:ambari' $AMBARI_URLBASE/hosts/<hawq-node>` |
#### <a id="ambari-rest-ex-put"></a>Starting/Stopping HAWQ and PXF Services
| Task |Command |
|----------------------|------------------------|
-| Start the HAWQ service. | `curl -u $AMBCREDS -X PUT -H 'X-Requested-By:ambari' -d '{"RequestInfo": {"context" :"Start HAWQ via REST"}, "Body": {"ServiceInfo": {"state": "STARTED"}}}' $AMBURLBASE/services/HAWQ` |
-| Stop the HAWQ service. | `curl -u $AMBCREDS -X PUT -H 'X-Requested-By:ambari' -d '{"RequestInfo": {"context" :"Stop HAWQ via REST"}, "Body": {"ServiceInfo": {"state": "INSTALLED"}}}' $AMBURLBASE/services/HAWQ` |
-| Start the PXF service. | `curl -u $AMBCREDS -X PUT -H 'X-Requested-By:ambari' -d '{"RequestInfo": {"context" :"Start PXF via REST"}, "Body": {"ServiceInfo": {"state": "STARTED"}}}' $AMBURLBASE//services/PXF` |
-| Stop the PXF service. | `curl -u $AMBCREDS -X PUT -H 'X-Requested-By:ambari' -d '{"RequestInfo": {"context" :"Stop PXF via REST"}, "Body": {"ServiceInfo": {"state": "INSTALLED"}}}' $AMBURLBASE/services/PXF` |
+| Start the HAWQ service. | `curl -u $AMBARI_CREDS -X PUT -H 'X-Requested-By:ambari' -d '{"RequestInfo": {"context" :"Start HAWQ via REST"}, "Body": {"ServiceInfo": {"state": "STARTED"}}}' $AMBARI_URLBASE/services/HAWQ` |
+| Stop the HAWQ service. | `curl -u $AMBARI_CREDS -X PUT -H 'X-Requested-By:ambari' -d '{"RequestInfo": {"context" :"Stop HAWQ via REST"}, "Body": {"ServiceInfo": {"state": "INSTALLED"}}}' $AMBARI_URLBASE/services/HAWQ` |
+| Start the PXF service. | `curl -u $AMBARI_CREDS -X PUT -H 'X-Requested-By:ambari' -d '{"RequestInfo": {"context" :"Start PXF via REST"}, "Body": {"ServiceInfo": {"state": "STARTED"}}}' $AMBARI_URLBASE//services/PXF` |
+| Stop the PXF service. | `curl -u $AMBARI_CREDS -X PUT -H 'X-Requested-By:ambari' -d '{"RequestInfo": {"context" :"Stop PXF via REST"}, "Body": {"ServiceInfo": {"state": "INSTALLED"}}}' $AMBARI_URLBASE/services/PXF` |
#### <a id="ambari-rest-ex-post"></a>Invoking HAWQ and PXF Service Actions
| Task |Command |
|----------------------|------------------------|
-| Run a HAWQ service check. | `curl -u $AMBCREDS -X POST -H 'X-Requested-By:ambari' -d '{"RequestInfo":{"context":"HAWQ Service Check","command":"HAWQ_SERVICE_CHECK"}, "Requests/resource_filters":[{ "service_name":"HAWQ"}]}' $AMBURLBASE/requests` |
-| Run a PXF service check. | `curl -u $AMBCREDS -X POST -H 'X-Requested-By:ambari' -d '{"RequestInfo":{"context":"PXF Service Check","command":"PXF_SERVICE_CHECK"}, "Requests/resource_filters":[{ "service_name":"PXF"}]}' $AMBURLBASE/requests` |
+| Run a HAWQ service check. | `curl -u $AMBARI_CREDS -X POST -H 'X-Requested-By:ambari' -d '{"RequestInfo":{"context":"HAWQ Service Check","command":"HAWQ_SERVICE_CHECK"}, "Requests/resource_filters":[{ "service_name":"HAWQ"}]}' $AMBARI_URLBASE/requests` |
+| Run a PXF service check. | `curl -u $AMBARI_CREDS -X POST -H 'X-Requested-By:ambari' -d '{"RequestInfo":{"context":"PXF Service Check","command":"PXF_SERVICE_CHECK"}, "Requests/resource_filters":[{ "service_name":"PXF"}]}' $AMBARI_URLBASE/requests` |
http://git-wip-us.apache.org/repos/asf/incubator-hawq-docs/blob/2c026427/markdown/admin/setuphawqopenv.html.md.erb
----------------------------------------------------------------------
diff --git a/markdown/admin/setuphawqopenv.html.md.erb b/markdown/admin/setuphawqopenv.html.md.erb
index 9d9b731..01e62ce 100644
--- a/markdown/admin/setuphawqopenv.html.md.erb
+++ b/markdown/admin/setuphawqopenv.html.md.erb
@@ -25,13 +25,13 @@ Perform the following steps to set up your HAWQ operating environment:
hawq-user@master$
```
-2. Set up your HAWQ operating environment by sourcing the `greenplum_path.sh` file:
+2. Set up your HAWQ operating environment by sourcing the `greenplum_path.sh` file. If you built HAWQ from source or downloaded the tarball, substitute the path to the installed or extracted `greenplum_path.sh` file \(for example `/opt/hawq-2.1.0.0/greenplum_path.sh`\):
``` shell
hawq-node$ source /usr/local/hawq/greenplum_path.sh
```
-
- If you built HAWQ from source or downloaded the tarball, substitute the path to the installed or extracted `greenplum_path.sh` file \(for example `/opt/hawq-2.1.0.0/greenplum_path.sh`\).
+
+ `source`ing `greenplum_path.sh` sets `PATH` to include the HAWQ `$GPHOME/bin/` directory. It also sets `LD_LIBRARY_PATH` to include the HAWQ libraries in `$GPHOME/lib/`.
3. Edit your `.bash_profile` or other shell initialization file to source `greenplum_path.sh` on login. For example, add:
@@ -78,4 +78,5 @@ The following table identifies some files and directories of interest in a defau
| /data/hawq/[master|segment]/pg_log/ | Default location of HAWQ master and segment log file directories |
| /etc/pxf/conf/ | PXF service and configuration files |
| /usr/lib/pxf/ | PXF service and plug-in shared libraries |
+| /var/log/pxf/ | PXF log file directory |
| /usr/hdp/current/ | HDP runtime and configuration files |
[7/7] incubator-hawq-docs git commit: HAWQ-1119 - create doc content
for PXF writable profiles (closes #46)
Posted by yo...@apache.org.
HAWQ-1119 - create doc content for PXF writable profiles (closes #46)
Project: http://git-wip-us.apache.org/repos/asf/incubator-hawq-docs/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-hawq-docs/commit/c8cb3027
Tree: http://git-wip-us.apache.org/repos/asf/incubator-hawq-docs/tree/c8cb3027
Diff: http://git-wip-us.apache.org/repos/asf/incubator-hawq-docs/diff/c8cb3027
Branch: refs/heads/release/2.1.0.0-incubating
Commit: c8cb3027671b58580c4b28118aefb4261085255b
Parents: df87f9a
Author: Lisa Owen <lo...@pivotal.io>
Authored: Wed Jan 18 11:37:19 2017 -0800
Committer: David Yozie <yo...@apache.org>
Committed: Wed Jan 18 11:37:19 2017 -0800
----------------------------------------------------------------------
.../source/subnavs/apache-hawq-nav-210.erb | 1 +
markdown/pxf/HDFSWritablePXF.html.md.erb | 414 +++++++++++++++++++
.../pxf/HawqExtensionFrameworkPXF.html.md.erb | 4 +
3 files changed, 419 insertions(+)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-hawq-docs/blob/c8cb3027/book/master_middleman/source/subnavs/apache-hawq-nav-210.erb
----------------------------------------------------------------------
diff --git a/book/master_middleman/source/subnavs/apache-hawq-nav-210.erb b/book/master_middleman/source/subnavs/apache-hawq-nav-210.erb
index 5d0306f..baab7b7 100644
--- a/book/master_middleman/source/subnavs/apache-hawq-nav-210.erb
+++ b/book/master_middleman/source/subnavs/apache-hawq-nav-210.erb
@@ -365,6 +365,7 @@
<li><a href="/docs/userguide/2.1.0.0-incubating/pxf/HivePXF.html">Accessing Hive Data</a></li>
<li><a href="/docs/userguide/2.1.0.0-incubating/pxf/HBasePXF.html">Accessing HBase Data</a></li>
<li><a href="/docs/userguide/2.1.0.0-incubating/pxf/JsonPXF.html">Accessing JSON Data</a></li>
+ <li><a href="/docs/userguide/2.1.0.0-incubating/pxf/HDFSWritablePXF.html">Writing Data to HDFS</a></li>
<li><a href="/docs/userguide/2.1.0.0-incubating/pxf/ReadWritePXF.html">Using Profiles to Read and Write Data</a></li>
<li><a href="/docs/userguide/2.1.0.0-incubating/pxf/PXFExternalTableandAPIReference.html">PXF External Tables and API</a></li>
<li><a href="/docs/userguide/2.1.0.0-incubating/pxf/TroubleshootingPXF.html">Troubleshooting PXF</a></li>
http://git-wip-us.apache.org/repos/asf/incubator-hawq-docs/blob/c8cb3027/markdown/pxf/HDFSWritablePXF.html.md.erb
----------------------------------------------------------------------
diff --git a/markdown/pxf/HDFSWritablePXF.html.md.erb b/markdown/pxf/HDFSWritablePXF.html.md.erb
new file mode 100644
index 0000000..b99b03e
--- /dev/null
+++ b/markdown/pxf/HDFSWritablePXF.html.md.erb
@@ -0,0 +1,414 @@
+---
+title: Writing Data to HDFS
+---
+
+The PXF HDFS plug-in supports writable external tables using the `HdfsTextSimple` and `SequenceWritable` profiles. You might create a writable table to export data from a HAWQ internal table to binary or text HDFS files.
+
+Use the `HdfsTextSimple` profile when writing text data. Use the `SequenceWritable` profile when dealing with binary data.
+
+This section describes how to use these PXF profiles to create writable external tables.
+
+**Note**: Tables that you create with writable profiles can only be used for INSERT operations. If you want to query inserted data, you must define a separate external readable table that references the new HDFS file using the equivalent readable profile.
+
+## <a id="pxfwrite_prereq"></a>Prerequisites
+
+Before working with HDFS file data using HAWQ and PXF, ensure that:
+
+- The HDFS plug-in is installed on all cluster nodes. See [Installing PXF Plug-ins](InstallPXFPlugins.html) for PXF plug-in installation information.
+- All HDFS users have read permissions to HDFS services.
+- HDFS write permissions are provided to a restricted set of users.
+
+## <a id="hdfsplugin_writeextdata"></a>Writing to PXF External Tables
+The PXF HDFS plug-in supports two writable profiles: `HdfsTextSimple` and `SequenceWritable`.
+
+Use the following syntax to create a HAWQ external writable table representing HDFS data:�
+
+``` sql
+CREATE WRITABLE EXTERNAL TABLE <table_name>
+ ( <column_name> <data_type> [, ...] | LIKE <other_table> )
+LOCATION ('pxf://<host>[:<port>]/<path-to-hdfs-file>
+ ?PROFILE=HdfsTextSimple|SequenceWritable[&<custom-option>=<value>[...]]')
+FORMAT '[TEXT|CSV|CUSTOM]' (<formatting-properties>);
+```
+
+HDFS-plug-in-specific keywords and values used in the [CREATE EXTERNAL TABLE](../reference/sql/CREATE-EXTERNAL-TABLE.html) call are described in the table below.
+
+| Keyword | Value |
+|-------|-------------------------------------|
+| \<host\>[:\<port\>] | The HDFS NameNode and port. |
+| \<path-to-hdfs-file\> | The path to the file in the HDFS data store. |
+| PROFILE | The `PROFILE` keyword must specify one of the values `HdfsTextSimple` or `SequenceWritable`. |
+| \<custom-option\> | \<custom-option\> is profile-specific. These options are discussed in the next topic.|
+| FORMAT 'TEXT' | Use '`TEXT`' `FORMAT` with the `HdfsTextSimple` profile to create a plain-text-delimited file at the location specified by \<path-to-hdfs-file\>. The `HdfsTextSimple` '`TEXT`' `FORMAT` supports only the built-in `(delimiter=<delim>)` \<formatting-property\>. |
+| FORMAT 'CSV' | Use '`CSV`' `FORMAT` with the `HdfsTextSimple` profile to create a comma-separated-value file at the location specified by \<path-to-hdfs-file\>. |
+| FORMAT 'CUSTOM' | Use the `'CUSTOM'` `FORMAT` with the `SequenceWritable` profile. The `SequenceWritable` '`CUSTOM`' `FORMAT` supports only the built-in `(formatter='pxfwritable_export)` (write) and `(formatter='pxfwritable_import)` (read) \<formatting-properties\>.
+
+**Note**: When creating PXF external tables, you cannot use the `HEADER` option in your `FORMAT` specification.
+
+## <a id="profile_hdfstextsimple"></a>Custom Options
+
+The `HdfsTextSimple` and `SequenceWritable` profiles support the following custom options:
+
+| Option | Value Description | Profile |
+|-------|-------------------------------------|--------|
+| COMPRESSION_CODEC | The compression codec Java class name. If this option is not provided, no data compression is performed. Supported compression codecs include: `org.apache.hadoop.io.compress.DefaultCodec` and `org.apache.hadoop.io.compress.BZip2Codec` | HdfsTextSimple, SequenceWritable |
+| COMPRESSION_CODEC | `org.apache.hadoop.io.compress.GzipCodec` | HdfsTextSimple |
+| COMPRESSION_TYPE | The compression type to employ; supported values are `RECORD` (the default) or `BLOCK`. | HdfsTextSimple, SequenceWritable |
+| DATA-SCHEMA | The name of the writer serialization/deserialization class. The jar file in which this class resides must be in the PXF classpath. This option is required for the `SequenceWritable` profile and has no default value. | SequenceWritable|
+| THREAD-SAFE | Boolean value determining if a table query can run in multi-threaded mode. The default value is `TRUE`. Set this option to `FALSE` to handle all requests in a single thread for operations that are not thread-safe (for example, compression). | HdfsTextSimple, SequenceWritable|
+
+## <a id="profile_hdfstextsimple"></a>HdfsTextSimple Profile
+
+Use the `HdfsTextSimple` profile when writing delimited data to a plain text file where each line is a single record.
+
+Writable tables created using the `HdfsTextSimple` profile can optionally use record or block compression. The following compression codecs are supported:
+
+- org.apache.hadoop.io.compress.DefaultCodec
+- org.apache.hadoop.io.compress.GzipCodec
+- org.apache.hadoop.io.compress.BZip2Codec
+
+The `HdfsTextSimple` profile supports the following \<formatting-properties\>:
+
+| Keyword | Value |
+|-------|-------------------------------------|
+| delimiter | The delimiter character to use when writing the file. Default value is a comma `,`.|
+
+
+### <a id="profile_hdfstextsimple_writing"></a>Example: Writing Data Using the HdfsTextSimple Profile
+
+This example uses the data schema introduced in [Example: Using the HdfsTextSimple Profile] (HDFSFileDataPXF.html#profile_hdfstextsimple_query):
+
+
+| Field Name | Data Type |
+|-------|-------------------------------------|
+| location | text |
+| month | text |
+| number\_of\_orders | int |
+| total\_sales | float8 |
+
+This example also uses the HAWQ table `pxf_hdfs_textsimple` created in that exercise and expects it to exist.
+
+
+Perform the following operations to use the PXF `HdfsTextSimple` profile to create a HAWQ writable external table with the same data schema as defined above. You will also create a separate external readable table to read the associated HDFS file.
+
+1. Create a writable HAWQ external table with the data schema described above. Write to the HDFS file `/data/pxf_examples/pxfwritable_hdfs_textsimple1`. Create the table specifying a comma `,` as the delimiter:
+
+ ``` sql
+ gpadmin=# CREATE WRITABLE EXTERNAL TABLE pxf_hdfs_writabletbl_1(location text, month text, num_orders int, total_sales float8)
+ LOCATION ('pxf://namenode:51200/data/pxf_examples/pxfwritable_hdfs_textsimple1?PROFILE=HdfsTextSimple')
+ FORMAT 'TEXT' (delimiter=E',');
+ ```
+
+ The `FORMAT` subclause `delimiter` value is specified as the single ascii comma character `,`. `E` escapes the character.
+
+2. Write a few records to the `pxfwritable_hdfs_textsimple1` HDFS file by invoking the SQL `INSERT` command on `pxf_hdfs_writabletbl_1`:
+
+ ``` sql
+ gpadmin=# INSERT INTO pxf_hdfs_writabletbl_1 VALUES ( 'Frankfurt', 'Mar', 777, 3956.98 );
+ gpadmin=# INSERT INTO pxf_hdfs_writabletbl_1 VALUES ( 'Cleveland', 'Oct', 3812, 96645.37 );
+ ```
+
+3. Insert the contents of the `pxf_hdfs_textsimple` table created in [Example: Using the HdfsTextSimple Profile] (HDFSFileDataPXF.html#profile_hdfstextsimple_query) into `pxf_hdfs_writabletbl_1`:
+
+ ``` sql
+ gpadmin=# INSERT INTO pxf_hdfs_writabletbl_1 SELECT * FROM pxf_hdfs_textsimple;
+ ```
+
+4. View the file contents in HDFS:
+
+ ``` shell
+ $ hdfs dfs -cat /data/pxf_examples/pxfwritable_hdfs_textsimple1/*
+ Frankfurt,Mar,777,3956.98
+ Cleveland,Oct,3812,96645.37
+ Prague,Jan,101,4875.33
+ Rome,Mar,87,1557.39
+ Bangalore,May,317,8936.99
+ Beijing,Jul,411,11600.67
+ ```
+
+ Because you specified comma `,` as the delimiter, this character is the field separator used in each record of the HDFS file.
+
+5. Querying an external writable table is not supported in HAWQ. To query data from the newly-created HDFS file, create a readable external HAWQ table referencing the HDFS file:
+
+ ``` sql
+ gpadmin=# CREATE EXTERNAL TABLE pxf_hdfs_textsimple_r1(location text, month text, num_orders int, total_sales float8)
+ LOCATION ('pxf://namenode:51200/data/pxf_examples/pxfwritable_hdfs_textsimple1?PROFILE=HdfsTextSimple')
+ FORMAT 'CSV';
+ ```
+
+ Specify the `'CSV'` `FORMAT` for the readable table, because you created the writable table with a comma `,` as the delimiter character.
+
+6. Query the readable external table `pxf_hdfs_textsimple_r1`:
+
+ ``` sql
+ gpadmin=# SELECT * FROM pxf_hdfs_textsimple_r1;
+ ```
+
+ ``` pre
+ location | month | num_orders | total_sales
+ -----------+-------+------------+-------------
+ Frankfurt | Mar | 777 | 3956.98
+ Cleveland | Oct | 3812 | 96645.37
+ Prague | Jan | 101 | 4875.33
+ Rome | Mar | 87 | 1557.39
+ Bangalore | May | 317 | 8936.99
+ Beijing | Jul | 411 | 11600.67
+ (6 rows)
+ ```
+
+ The table includes the records you individually inserted, as well as the full contents of the `pxf_hdfs_textsimple` table.
+
+7. Create a second HAWQ external writable table, this time using Gzip compression and employing a colon `:` as the delimiter:
+
+ ``` sql
+ gpadmin=# CREATE WRITABLE EXTERNAL TABLE pxf_hdfs_writabletbl_2 (location text, month text, num_orders int, total_sales float8)
+ LOCATION ('pxf://namenode:51200/data/pxf_examples/pxfwritable_hdfs_textsimple2?PROFILE=HdfsTextSimple&COMPRESSION_CODEC=org.apache.hadoop.io.compress.GzipCodec')
+ FORMAT 'TEXT' (delimiter=E':');
+ ```
+
+8. Write a few records to the `pxfwritable_hdfs_textsimple2` HDFS file by inserting into the `pxf_hdfs_writabletbl_2` table:
+
+ ``` sql
+ gpadmin=# INSERT INTO pxf_hdfs_writabletbl_2 VALUES ( 'Frankfurt', 'Mar', 777, 3956.98 );
+ gpadmin=# INSERT INTO pxf_hdfs_writabletbl_2 VALUES ( 'Cleveland', 'Oct', 3812, 96645.37 );
+ ```
+
+9. View the file contents in HDFS; use the `-text` option to `hdfs dfs` to view the compressed data as text:
+
+ ``` shell
+ $ hdfs dfs -text /data/pxf_examples/pxfwritable_hdfs_textsimple2/*
+ Frankfurt:Mar:777:3956.98
+ Cleveland:Oct:3812:96645.3
+ ```
+
+ Notice that the colon `:` is the field separator in the HDFS file.
+
+ As described in Step 5 above, to query data from the newly-created HDFS file named `pxfwritable_hdfs_textsimple2`, you can create a readable external HAWQ table referencing this HDFS file.
+
+
+## <a id="profile_hdfsseqwritable_using"></a>SequenceWritable Profile
+
+Use the HDFS plug-in `SequenceWritable` profile when writing SequenceFile format files. Files of this type consist of binary key/value pairs. Sequence files are a common data transfer format between MapReduce jobs.
+
+SequenceFile format files can optionally use record or block compression. The following compression codecs are supported:
+
+- org.apache.hadoop.io.compress.DefaultCodec
+- org.apache.hadoop.io.compress.BZip2Codec
+
+When using the `SequenceWritable` profile to write a SequenceFile format file, you must provide the name of the Java class to use for serializing/deserializing the data. This class must provide read and write methods for the fields in the schema associated with the data.
+
+### <a id="profile_hdfsseqwritable_writing"></a>Example: Writing Data Using the SequenceWritable Profile
+
+In this example, you will create a Java class named `PxfExample_CustomWritable` that will serialize/deserialize the fields in the sample schema used in previous examples. You will then use this class to access a writable external table created with the `SequenceWritable` profile.
+
+Perform the following steps to create the Java class and writable table.
+
+1. Prepare to create the sample Java class:
+
+ ``` shell
+ $ mkdir -p pxfex/com/hawq/example/pxf/hdfs/writable/dataschema
+ $ cd pxfex/com/hawq/example/pxf/hdfs/writable/dataschema
+ $ vi PxfExample_CustomWritable.java
+ ```
+
+2. Copy and paste the following text into the `PxfExample_CustomWritable.java` file:
+
+ ``` java
+ package com.hawq.example.pxf.hdfs.writable.dataschema;
+
+ import org.apache.hadoop.io.*;
+import java.io.DataInput;
+import java.io.DataOutput;
+import java.io.IOException;
+import java.lang.reflect.Field;
+
+ /**
+ * PxfExample_CustomWritable class - used to serialize and deserialize data with
+ * text, int, and float data types
+ */
+public class PxfExample_CustomWritable implements Writable {
+
+ public String st1, st2;
+ public int int1;
+ public float ft;
+
+ public PxfExample_CustomWritable() {
+ st1 = new String("");
+ st2 = new String("");
+ int1 = 0;
+ ft = 0.f;
+ }
+
+ public PxfExample_CustomWritable(int i1, int i2, int i3) {
+
+ st1 = new String("short_string___" + i1);
+ st2 = new String("short_string___" + i1);
+ int1 = i2;
+ ft = i1 * 10.f * 2.3f;
+
+ }
+
+ String GetSt1() {
+ return st1;
+ }
+
+ String GetSt2() {
+ return st2;
+ }
+
+ int GetInt1() {
+ return int1;
+ }
+
+ float GetFt() {
+ return ft;
+ }
+
+ @Override
+ public void write(DataOutput out) throws IOException {
+
+ Text txt = new Text();
+ txt.set(st1);
+ txt.write(out);
+ txt.set(st2);
+ txt.write(out);
+
+ IntWritable intw = new IntWritable();
+ intw.set(int1);
+ intw.write(out);
+
+ FloatWritable fw = new FloatWritable();
+ fw.set(ft);
+ fw.write(out);
+ }
+
+ @Override
+ public void readFields(DataInput in) throws IOException {
+
+ Text txt = new Text();
+ txt.readFields(in);
+ st1 = txt.toString();
+ txt.readFields(in);
+ st2 = txt.toString();
+
+ IntWritable intw = new IntWritable();
+ intw.readFields(in);
+ int1 = intw.get();
+
+ FloatWritable fw = new FloatWritable();
+ fw.readFields(in);
+ ft = fw.get();
+ }
+
+ public void printFieldTypes() {
+ Class myClass = this.getClass();
+ Field[] fields = myClass.getDeclaredFields();
+
+ for (int i = 0; i < fields.length; i++) {
+ System.out.println(fields[i].getType().getName());
+ }
+ }
+}
+ ```
+
+3. Compile and create a Java class JAR file for `PxfExample_CustomWritable`:
+
+ ``` shell
+ $ javac -classpath /usr/hdp/2.5.3.0-37/hadoop/hadoop-common.jar PxfExample_CustomWritable.java
+ $ cd ../../../../../../../
+ $ jar cf pxfex-customwritable.jar com
+ $ cp pxfex-customwritable.jar /tmp/
+ ```
+
+ (Your Hadoop library classpath may differ.)
+
+4. Include the new jar file in the PXF Agent classpath by updating the `pxf-public.classpath` file. If you use Ambari to manage your cluster, add the following line via the Ambari UI and restart the PXF Agent:
+
+ ``` pre
+ /tmp/pxfex-customwritable.jar
+ ```
+
+ If you have a command-line-managed HAWQ cluster, perform the following steps on each node in your HAWQ cluster:
+ - Directly edit `/etc/pxf/conf/pxf-public.classpath` and add the line above.
+ - Restart the PXF Agent:
+
+ ``` shell
+ $ sudo service pxf-service restart
+ ```
+
+5. Use the PXF `SequenceWritable` profile to create a writable HAWQ external table. Identify the serialization/deserialization Java class you created above in the `DATA-SCHEMA` \<custom-option\>. Use `BLOCK` mode compression with `BZip2` when creating the writable table.
+
+ ``` sql
+ gpadmin=# CREATE WRITABLE EXTERNAL TABLE pxf_tbl_seqwrit (location text, month text, number_of_orders integer, total_sales real)
+ LOCATION ('pxf://namenode:51200/data/pxf_examples/pxf_seqwrit_file?PROFILE=SequenceWritable&DATA-SCHEMA=com.hawq.example.pxf.hdfs.writable.dataschema.PxfExample_CustomWritable&COMPRESSION_TYPE=BLOCK&COMPRESSION_CODEC=org.apache.hadoop.io.compress.BZip2Codec')
+ FORMAT 'CUSTOM' (formatter='pxfwritable_export');
+ ```
+
+ Notice that the `'CUSTOM'` `FORMAT` \<formatting-properties\> specify the built-in `pxfwritable_export` formatter.
+
+6. Insert some data into `pxf_tbl_seqwrit`:
+
+ ``` sql
+ gpadmin=# INSERT INTO pxf_tbl_seqwrit VALUES ( 'Frankfurt', 'Mar', 777, 3956.98 );
+ gpadmin=# INSERT INTO pxf_tbl_seqwrit VALUES ( 'Cleveland', 'Oct', 3812, 96645.37 );
+ ```
+
+6. Recall that querying an external writable table is not supported in HAWQ. To read the newly-created writable table, create a HAWQ readable external table referencing the writable table's HDFS file:
+
+ ``` sql
+ gpadmin=# CREATE EXTERNAL TABLE read_pxf_tbl_seqwrit (location text, month text, number_of_orders integer, total_sales real)
+ LOCATION ('pxf://namenode:51200/data/pxf_examples/pxf_seqwrit_file?PROFILE=SequenceWritable&DATA-SCHEMA=com.hawq.example.pxf.hdfs.writable.dataschema.PxfExample_CustomWritable')
+ FORMAT 'CUSTOM' (formatter='pxfwritable_import');
+ ```
+ The `DATA-SCHEMA` \<custom-option\> must be specified when reading an HDFS file via the `SequenceWritable` profile. Compression-related options need not be provided.
+
+6. Query the readable external table `read_pxf_tbl_seqwrit`:
+
+ ``` sql
+ gpadmin=# SELECT * FROM read_pxf_tbl_seqwrit;
+ ```
+
+ ``` pre
+ location | month | number_of_orders | total_sales
+ -----------+-------+------------------+-------------
+ Frankfurt | Mar | 777 | 3956.98
+ Cleveland | Oct | 3812 | 96645.4
+ (2 rows)
+ ```
+
+## <a id="recordkeyinkey-valuefileformats"></a>Reading the Record Key
+
+When a HAWQ external table references a SequenceFile or another file format that store rows in a key-value format, you can access the key values in HAWQ queries by using the `recordkey` keyword as a field name.
+
+The field type of `recordkey` must correspond to the key type, much as the other fields must match the HDFS data.�
+
+`recordkey` can be any of the following Hadoop types:
+
+- BooleanWritable
+- ByteWritable
+- DoubleWritable
+- FloatWritable
+- IntWritable
+- LongWritable
+- Text
+
+### <a id="example1"></a>Example: Using Record Keys
+
+Create an external readable table to access the record keys from the writable table `pxf_tbl_seqwrit` that you created in [Example: Writing Data Using the SequenceWritable Profile](#profile_hdfsseqwritable_writing). The `recordkey` is of type `int8`.
+
+``` sql
+gpadmin=# CREATE EXTERNAL TABLE read_pxf_tbl_seqwrit_RECKEY (recordkey int8, location text, month text, number_of_orders integer, total_sales real)
+ LOCATION ('pxf://namenode:51200/data/pxf_examples/pxf_seqwrit_file?PROFILE=SequenceWritable&DATA-SCHEMA=com.hawq.example.pxf.hdfs.writable.dataschema.PxfExample_CustomWritable')
+ FORMAT 'CUSTOM' (formatter='pxfwritable_import');
+gpadmin=# SELECT * FROM read_pxf_tbl_seqwrit_RECKEY;
+```
+
+``` pre
+ recordkey | location | month | number_of_orders | total_sales
+-----------+-------------+-------+------------------+-------------
+ 0 | Frankfurt | Mar | 777 | 3956.98
+ 0 | Cleveland | Oct | 3812 | 96645.4
+(2 rows)
+```
+
+The `recordkey` is `0` because you did not identify a record key when you inserted entries into the writable table.
http://git-wip-us.apache.org/repos/asf/incubator-hawq-docs/blob/c8cb3027/markdown/pxf/HawqExtensionFrameworkPXF.html.md.erb
----------------------------------------------------------------------
diff --git a/markdown/pxf/HawqExtensionFrameworkPXF.html.md.erb b/markdown/pxf/HawqExtensionFrameworkPXF.html.md.erb
index 578d13f..72ca3b6 100644
--- a/markdown/pxf/HawqExtensionFrameworkPXF.html.md.erb
+++ b/markdown/pxf/HawqExtensionFrameworkPXF.html.md.erb
@@ -32,6 +32,10 @@ PXF allows users to create custom connectors to access other parallel data store
This topic describes how to access JSON data using PXF.
+- **[Writing Data to HDFS](../pxf/HDFSWritablePXF.html)**
+
+ This topic describes how to write to HDFS using PXF.
+
- **[Using Profiles to Read and Write Data](../pxf/ReadWritePXF.html)**
PXF profiles are collections of common metadata attributes that can be used to simplify the reading and writing of data. You can use any of the built-in profiles that come with PXF or you can create your own.
[6/7] incubator-hawq-docs git commit: HAWQ-1263 docs - miscellaneous
improvements (closes #86)
Posted by yo...@apache.org.
HAWQ-1263 docs - miscellaneous improvements (closes #86)
Project: http://git-wip-us.apache.org/repos/asf/incubator-hawq-docs/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-hawq-docs/commit/df87f9ae
Tree: http://git-wip-us.apache.org/repos/asf/incubator-hawq-docs/tree/df87f9ae
Diff: http://git-wip-us.apache.org/repos/asf/incubator-hawq-docs/diff/df87f9ae
Branch: refs/heads/release/2.1.0.0-incubating
Commit: df87f9ae8e964c5c0722f75127fa2b3029f62971
Parents: 87328a5
Author: Lisa Owen <lo...@pivotal.io>
Authored: Mon Jan 16 16:55:38 2017 -0800
Committer: David Yozie <yo...@apache.org>
Committed: Mon Jan 16 16:55:38 2017 -0800
----------------------------------------------------------------------
...reating-external-tables-examples.html.md.erb | 2 +-
markdown/ddl/ddl-database.html.md.erb | 46 +++++-----
markdown/install/aws-config.html.md.erb | 93 ++++++++++++--------
.../reference/sql/CREATE-DATABASE.html.md.erb | 14 +--
4 files changed, 87 insertions(+), 68 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-hawq-docs/blob/df87f9ae/markdown/datamgmt/load/creating-external-tables-examples.html.md.erb
----------------------------------------------------------------------
diff --git a/markdown/datamgmt/load/creating-external-tables-examples.html.md.erb b/markdown/datamgmt/load/creating-external-tables-examples.html.md.erb
index 8cdbff1..7d38971 100644
--- a/markdown/datamgmt/load/creating-external-tables-examples.html.md.erb
+++ b/markdown/datamgmt/load/creating-external-tables-examples.html.md.erb
@@ -19,7 +19,7 @@ Creates a readable external table, `ext_expenses`, using the `gpfdist` protocol.
``` sql
=# CREATE EXTERNAL TABLE ext_expenses
( name text, date date, amount float4, category text, desc1 text )
- LOCATION ('gpfdist://etlhost-1:8081/*', 'gpfdist://etlhost-1:8082/*')
+ LOCATION ('gpfdist://etlhost-1:8081/*')
FORMAT 'TEXT' (DELIMITER '|');
```
http://git-wip-us.apache.org/repos/asf/incubator-hawq-docs/blob/df87f9ae/markdown/ddl/ddl-database.html.md.erb
----------------------------------------------------------------------
diff --git a/markdown/ddl/ddl-database.html.md.erb b/markdown/ddl/ddl-database.html.md.erb
index 2ef9f9f..d643fae 100644
--- a/markdown/ddl/ddl-database.html.md.erb
+++ b/markdown/ddl/ddl-database.html.md.erb
@@ -2,77 +2,75 @@
title: Creating and Managing Databases
---
-A HAWQ system is a single instance of HAWQ. There can be several separate HAWQ systems installed, but usually just one is selected by environment variable settings. See your HAWQ administrator for details.
-
-There can be multiple databases in a HAWQ system. This is different from some database management systems \(such as Oracle\) where the database instance *is* the database. Although you can create many databases in a HAWQ system, client programs can connect to and access only one database at a time \u2014 you cannot cross-query between databases.
+Your HAWQ deployment may have multiple databases. This is different from some database management systems \(such as Oracle\) where the database instance *is* the database. Although you can create many databases in a HAWQ system, client programs can connect to and access only one database at a time \u2014 you cannot cross-query between databases.
## <a id="topic3"></a>About Template Databases
-Each new database you create is based on a *template*. HAWQ provides a default database, *template1*. Use *template1* to connect to HAWQ for the first time. HAWQ uses *template1* to create databases unless you specify another template. Do not create any objects in *template1* unless you want those objects to be in every database you create.
+Each new database you create is based on a *template*. HAWQ provides a default database, `template1`. HAWQ uses `template1` to create databases unless you specify another template. Do not create any objects in `template1` unless you want those objects to be in every database you create.
-HAWQ uses two other database templates, *template0* and *postgres*, internally. Do not drop or modify *template0* or *postgres*. You can use *template0* to create a completely clean database containing only the standard objects predefined by HAWQ at initialization, especially if you modified *template1*.
+HAWQ uses two other database templates internally, `template0` and `postgres`. Do not drop or modify `template0` or `postgres`. You can use `template0` to create a completely clean database containing only the standard objects predefined by HAWQ at initialization. This may be necessary if you have modified `template1`.
## <a id="topic4"></a>Creating a Database
-The `CREATE DATABASE` command creates a new database. For example:
+You must have appropriate privileges or be a HAWQ superuser to create a database. If you do not have the correct privileges, you cannot create a database. The HAWQ administrator must either grant you the necessary privileges or create a database for you.
+
+The [CREATE DATABASE](../reference/sql/CREATE-DATABASE.html) command creates a new database. For example:
``` sql
-=> CREATE DATABASE new_dbname;
+=# CREATE DATABASE <new_dbname>;
```
-To create a database, you must have privileges to create a database or be a HAWQ superuser. If you do not have the correct privileges, you cannot create a database. The HAWQ administrator must either give you the necessary privileges or to create a database for you.
-
-You can also use the client program `createdb` to create a database. For example, running the following command in a command line terminal connects to HAWQ using the provided host name and port and creates a database named *mydatabase*:
+You can also use the client program `createdb` to create a database. For example, running the following command connects to HAWQ on the host named `hmaster` at port `5432` and creates a database named `mydatabase`:
``` shell
-$ createdb -h masterhost -p 5432 mydatabase
+$ createdb -h hmaster -p 5432 mydatabase
```
-The host name and port must match the host name and port of the installed HAWQ system.
+The host name and port must match the host name and port of the HAWQ master node.
-Some objects, such as roles, are shared by all the databases in a HAWQ system. Other objects, such as tables that you create, are known only in the database in which you create them.
+Some objects, such as roles, are shared by all of the databases in a HAWQ deployment. Other objects, such as tables that you create, are known only to the database in which you create them.
### <a id="topic5"></a>Cloning a Database
-By default, a new database is created by cloning the standard system database template, *template1*. Any database can be used as a template when creating a new database, thereby providing the capability to 'clone' or copy an existing database and all objects and data within that database. For example:
+By default, a new database is created by cloning the standard system database template, `template1`. You can identify an alternate template to use for database creation. As mentioned in the section above, you can specify `template0` as the template:
``` sql
-=> CREATE DATABASE new_dbname TEMPLATE old_dbname
+=# CREATE DATABASE <new_dbname> WITH TEMPLATE template0;
```
## <a id="topic6"></a>Viewing the List of Databases
-If you are working in the `psql` client program, you can use the `\l` meta-command to show the list of databases and templates in your HAWQ system. If using another client program and you are a superuser, you can query the list of databases from the `pg_database` system catalog table. For example:
+You can use the `\l` meta-command of the `psql` client subsystem to display the list of databases and templates in your HAWQ deployment. If you are a database superuser, you can query the list of databases from the `pg_database` system catalog table. For example:
``` sql
-=> SELECT datname FROM pg_database;
+=# SELECT datname FROM pg_database;
```
## <a id="topic7"></a>Altering a Database
-The ALTER DATABASE command changes database attributes such as owner, name, or default configuration attributes. For example, the following command alters a database by setting its default schema search path \(the `search_path` configuration parameter\):
+Use the [ALTER DATABASE](../reference/sql/ALTER-DATABASE.html) command to update database attributes including owner or name. You can also use ALTER DATABASE to update a database's default configuration. For example, the following command alters the `search_path` server configuration parameter to change the default schema search path for database `mydatabase`:
``` sql
-=> ALTER DATABASE mydatabase SET search_path TO myschema, public, pg_catalog;
+=# ALTER DATABASE mydatabase SET search_path TO myschema, public, pg_catalog;
```
To alter a database, you must be the owner of the database or a superuser.
## <a id="topic8"></a>Dropping a Database
-The `DROP DATABASE` command drops \(or deletes\) a database. It removes the system catalog entries for the database and deletes the database directory on disk that contains the data. You must be the database owner or a superuser to drop a database, and you cannot drop a database while you or anyone else is connected to it. Connect to `template1` \(or another database\) before dropping a database. For example:
+The [DROP DATABASE](../reference/sql/DROP-DATABASE.html) command drops \(deletes\) a database. It removes the system catalog entries for the database and deletes the data on disk. You must be the database owner or a superuser to drop a database, and you cannot drop a database while you or anyone else is connected to it. Connect to `template1` \(or another database\) before dropping a database. For example:
``` shell
-=> \c template1
+=# \c template1
```
``` sql
-=> DROP DATABASE mydatabase;
+=# DROP DATABASE mydatabase;
```
-You can also use the client program `dropdb` to drop a database. For example, the following command connects to HAWQ using the provided host name and port and drops the database *mydatabase*:
+You can also use the client program `dropdb` to drop a database. For example, the following command connects to HAWQ on the host named `hmaster` at port `5432` and drops the database named `mydatabase`:
``` shell
-$ dropdb -h masterhost -p 5432 mydatabase
+$ dropdb -h hmaster -p 5432 mydatabase
```
**Warning:** Dropping a database cannot be undone.
http://git-wip-us.apache.org/repos/asf/incubator-hawq-docs/blob/df87f9ae/markdown/install/aws-config.html.md.erb
----------------------------------------------------------------------
diff --git a/markdown/install/aws-config.html.md.erb b/markdown/install/aws-config.html.md.erb
index 21cadf5..1bb519e 100644
--- a/markdown/install/aws-config.html.md.erb
+++ b/markdown/install/aws-config.html.md.erb
@@ -14,31 +14,41 @@ For information about Amazon EC2, see the [EC2 User Guide](http://docs.aws.amazo
## <a id="topic_nhk_df4_2v"></a>Create and Launch HAWQ Instances
-Use the *Amazon EC2 Console* to launch instances and configure, start, stop, and terminate (delete) virtual servers. When you launch a HAWQ instance, you select and configure key attributes via the EC2 Console.
+Use the **Amazon EC2 Console** to launch instances and configure, start, stop, and terminate (delete) virtual servers. When you launch a HAWQ instance, you select and configure key attributes via the EC2 Console.
### <a id="topic_amitype"></a>Choose AMI Type
-An Amazon Machine Image (AMI) is a template that contains a software configuration including the operating system, application server, and applications that best suit your purpose. When configuring a HAWQ virtual instance, we recommend you use a *hardware virtualized* AMI running 64-bit Red Hat Enterprise Linux version 6.4 or 6.5 or 64-bit CentOS 6.4 or 6.5. Obtain the licenses and instances directly from the OS provider.
+An Amazon Machine Image (AMI) is a template that contains a specific software configuration including the operating system, application server, and applications that best suit your purpose. When configuring a HAWQ virtual instance, use a *hardware virtualized* (HVM) AMI supporting enhanced 10Gbps networking. Ensure the AMI is running 64-bit Red Hat Enterprise Linux version 6.4 or 6.5 or 64-bit CentOS 6.4 or 6.5. Obtain the licenses and instances directly from the OS provider.
### <a id="topic_selcfgstorage"></a>Consider Storage
-EC2 instances can be launched as either Elastic Block Store (EBS)-backed or instance store-backed.
+You can launch EC2 instances as either Elastic Block Store (EBS)-backed or instance store-backed. Choose the storage type based on the expected lifetime of your cluster and data.
-Instance store-backed storage is generally better performing than EBS and recommended for HAWQ's large data workloads. SSD (solid state) instance store is preferred over magnetic drives.
+#### <a id="topic_selcfgstorage_instance"></a>Instance Store-Backed
-**Note** EC2 *instance store* provides temporary block-level storage. This storage is located on disks that are physically attached to the host computer. While instance store provides high performance, powering off the instance causes data loss. Soft reboots preserve instance store data.
-
-Virtual devices for instance store volumes for HAWQ EC2 instance store instances are named `ephemeralN` (where *N* varies based on instance type). CentOS instance store block device are named `/dev/xvdletter` (where *letter* is a lower case letter of the alphabet).
+Use instance store-backed storage for short lived or transient clusters that do not require long-term persistence of data. While instance store-backed storage is generally better performing than EBS, it is not recommended for use in a production environment.
+
+**Warning**: EC2 instance store-backed storage provides *temporary* block-level storage. This storage is located on disks that are physically attached to the host computer. You will lose all instance store data when the AMI instance is powered off.
+
+
+#### <a id="topic_selcfgstorage_ebs"></a>EBS-Backed
+EBS volumes are reliable and highly-available. Use EBS-backed storage for longer running clusters where data must be quickly accessible and must remain available for a long period of time.
+
+
+#### <a id="topic_selcfgstorage_volume"></a>Volume Types
+When selecting between HDD and SSD volume types, the trade-offs are between speed, capacity, and cost. HDD volumes are less expensive and have greater disk capacity, but may be less performant. SSD (solid state drive) volumes are more performant, but costlier and typically have less disk capacity.
### <a id="topic_cfgplacegrp"></a>Configure Placement Group
-A placement group is a logical grouping of instances within a single availability zone that together participate in a low-latency, 10 Gbps network. Your HAWQ master and segment cluster instances should support enhanced networking and reside in a single placement group (and subnet) for optimal network performance.
+A placement group is a logical grouping of instances within a single availability zone that participate in a low-latency, 10 Gbps network. Your HAWQ master and segment cluster instances should support enhanced networking and reside in a single placement group (and subnet) for optimal network performance.
If your Ambari node is not a DataNode, locating the Ambari node instance in a subnet separate from the HAWQ master/segment placement group enables you to manage multiple HAWQ clusters from the single Ambari instance.
Amazon recommends that you use the same instance type for all instances in the placement group and that you launch all instances within the placement group at the same time.
-Membership in a placement group has some implications on your HAWQ cluster. Specifically, growing the cluster over capacity may require shutting down all HAWQ instances in the current placement group and restarting the instances to a new placement group. Instance store volumes are lost in this scenario.
+Membership in a placement group has some implications for your HAWQ cluster. Specifically, growing the cluster beyond the placement group capacity may require shutting down all HAWQ instances in the current placement group and restarting the instances in a new placement group. **Warning**: Instance store volumes are lost in this scenario.
+
+**Note**: If cluster down time during expansion is not acceptable for your HAWQ deployment, do not use placement groups.
### <a id="topic_selinsttype"></a>Select EC2 Instance Type
@@ -46,44 +56,53 @@ An EC2 instance type is a specific combination of CPU, memory, default storage,
Several instance store-backed EC2 instance types have shown acceptable performance for HAWQ nodes in development and production environments:
-| Instance Type | Env | vCPUs | Memory (GB) | Disk Capacity (GB) | Storage Type |
-|-------|-----|------|--------|----------|--------|
-| cc2.8xlarge | Dev | 32 | 60.5 | 4 x 840 | HDD |
-| d2.2xlarge | Dev | 8 | 60 | 6 x 2000 | HDD |
-| d2.4xlarge | Dev/QA | 16 | 122 | 12 x 2000 | HDD |
-| i2.8xlarge | Prod | 32 | 244 | 8 x 800 | SSD |
-| hs1.8xlarge | Prod | 16 | 117 | 24 x 2000 | HDD |
-| d2.8xlarge | Prod | 36 | 244 | 24 x 2000 | HDD |
+| Instance Type | Env | vCPUs | Memory (GB) | Disk Capacity (GB) | Storage Type | Network Speed |
+|-------|-----|------|--------|----------|--------|-------|
+| d2.2xlarge | Dev | 8 | 60 | 6 x 2000 | HDD | High |
+| d2.4xlarge | Dev/QA | 16 | 122 | 12 x 2000 | HDD | High |
+| c3.8xlarge | Dev/QA | 32 | 60 | 2 x 320 | SSD | 10 Gigabit |
+| r3.8xlarge | Dev/QA | 32 | 244 | 2 x 320 | SSD | 10 Gigabit |
+| i2.8xlarge | Prod | 32 | 244 | 8 x 800 | SSD | 10 Gigabit |
+| d2.8xlarge | Prod | 36 | 244 | 24 x 2000 | HDD | 10 Gigabit |
-For optimal network performance, the chosen HAWQ instance type should support EC2 enhanced networking. Enhanced networking results in higher performance, lower latency, and lower jitter. Refer to [Enhanced Networking on Linux Instances](http://docs.aws.amazon.com/AWSEC2/latest/UserGuide/enhanced-networking.html) for detailed information on enabling enhanced networking in your instances.
+**Note**: This list is not exhaustive. You may find other instance types with similar specifications suitable for your HAWQ deployment.
+
+For optimal network performance, the chosen HAWQ instance type should support EC2 enhanced networking. Enhanced networking results in higher performance, lower latency, and lower jitter. Refer to [Enhanced Networking on Linux Instances](http://docs.aws.amazon.com/AWSEC2/latest/UserGuide/enhanced-networking.html) for detailed information on enabling enhanced networking in supported EC2 instance types.
-All instance types identified in the table above support enhanced networking.
-### <a id="topic_cfgnetw"></a>Configure Networking
+### <a id="topic_cfgnetw"></a>Configure Networking and VPC
-Your HAWQ cluster instances should be in a single VPC and on the same subnet. Instances are always assigned a VPC internal IP address. This internal IP address should be used for HAWQ communication between hosts. You can also use the internal IP address to access an instance from another instance within the HAWQ VPC.
+Place your HAWQ cluster instances in a single VPC and on the same subnet. Instances are always assigned a VPC internal IP address. Use this internal IP address for HAWQ communication between hosts. You can also use the internal IP address to access an instance from another instance within the HAWQ VPC.
You may choose to locate your Ambari node on a separate subnet in the VPC. Both a public IP address for the instance and an Internet gateway configured for the EC2 VPC are required to access the Ambari instance from an external source and for the instance to access the Internet.
-Ensure your Ambari and HAWQ master instances are each assigned a public IP address for external and internet access. We recommend you also assign an Elastic IP Address to the HAWQ master instance.
+Ensure your Ambari and HAWQ master instances are each assigned a public IP address for external and internet access. Also assign an Elastic IP Address to the HAWQ master instance.
###Configure Security Groups<a id="topic_cfgsecgrp"></a>
A security group is a set of rules that control network traffic to and from your HAWQ instance. One or more rules may be associated with a security group, and one or more security groups may be associated with an instance.
-To configure HAWQ communication between nodes in the HAWQ cluster, include and open the following ports in the appropriate security group for the HAWQ master and segment nodes:
+To configure HAWQ communication between nodes in a HAWQ cluster, include and open appropriate ports in the security group for the HAWQ master and segment nodes. For example, if you have a single VPC, you may create a single security group named `hawq-cluster-sg` for your cluster, and configure this security group to open the following ports:
+
+| Port | Protocol | Application | Source |
+|-------|----------|--------------|-------------|
+| -1 | ICMP | `ping` | `hawq-cluster-sg` |
+| 5432 | TCP | HAWQ/Postgres | cidr 0.0.0.0/0 |
+| 50700 | TCP | HDFS NameNode | cidr 0.0.0.0/0 |
+| 0-65535 | TCP | `ssh`, HAWQ segment communication | `hawq-cluster-sg` |
+| 0-65535 | UDP | HAWQ segment communication | `hawq-cluster-sg` |
-| Port | Application |
-|-------|-------------------------------------|
-| 22 | ssh - secure connect to other hosts |
+This configuration allows communication between the HAWQ segment nodes. It also allows `ping` on and `ssh` between all instances in the `hawq-cluster-sg` security group. The HAWQ master and HDFS NameNode ports are open with the above settings.
-To allow access to/from a source external to the Ambari management node, include and open the following ports in an appropriate security group for your Ambari node:
+Open and/or restrict these and any additional ports required for your HAWQ deployment environment.
-| Port | Application |
-|-------|-------------------------------------|
-| 22 | ssh - secure connect to other hosts |
-| 8080 | Ambari - HAWQ admin/config web console |
+Include and open the following ports in an appropriate security group for your Ambari node to allow access to/from a source external to this node:
+
+| Port | Protocol | Application |
+|-------|-----------------|--------------------|
+| 22 | TCP | `ssh` - secure shell to/from other hosts |
+| 8080 | TCP | Ambari - HAWQ admininstration web console |
###Generate Key Pair<a id="topic_cfgkeypair"></a>
@@ -91,21 +110,23 @@ AWS uses public-key cryptography to secure the login information for your instan
A key pair for an EC2 instance consists of a *public key* that AWS stores, and a *private key file* that you maintain. Together, they allow you to connect to your instance securely. The private key file name typically has a `.pem` suffix.
-This example logs into an into EC2 instance from an external location with the private key file `my-test.pem` as user `user1`. In this example, the instance is configured with the public IP address `192.0.2.0` and the private key file resides in the current directory.
+The following example logs into an into EC2 instance from an external location with the private key file `my-test.pem` as user `user1`. In this example, the instance is configured with the public IP address `192.0.2.0` and the private key file resides in the current directory.
```shell
-$ ssh -i my-test.pem user1@192.0.2.0
+$ ssh -i ./my-test.pem user1@192.0.2.0
```
##Additional HAWQ Considerations <a id="topic_mj4_524_2v"></a>
-After launching your HAWQ instance, you will connect to and configure the instance. The *Instances* page of the EC2 Console lists the running instances and their associated network access information.
+After launching your HAWQ instance, you will connect to and configure the instance. The **Instances** page of the EC2 Console lists the running instances and their associated network access information.
-Before installing HAWQ, set up the EC2 instances as you would local host server machines. Configure the host operating system, configure host network information (for example, update the `/etc/hosts` file), set operating system parameters, and install operating system packages. For information about how to prepare your operating system environment for HAWQ, see [Apache HAWQ System Requirements](../requirements/system-requirements.html) and [Select HAWQ Host Machines](../install/select-hosts.html).
+Before installing HAWQ, set up the EC2 instances as you would local host server machines. Configure the host operating system, configure host network information (for example, update the `/etc/hosts` file), set operating system parameters, and install operating system packages. [Apache HAWQ System Requirements](../requirements/system-requirements.html) and [Select HAWQ Host Machines](../install/select-hosts.html) provide the information necessary to prepare your operating system environment for HAWQ.
###Passwordless SSH Configuration<a id="topic_pwdlessssh_cc"></a>
-HAWQ hosts will be configured during the installation process to use passwordless SSH for intra-cluster communications. Temporary password-based authentication must be enabled on each HAWQ host in preparation for this configuration. Password authentication is typically disabled by default in cloud images. Update the cloud configuration in `/etc/cloud/cloud.cfg` to enable password authentication in your AMI(s). Set `ssh_pwauth: True` in this file. If desired, disable password authentication after HAWQ installation by setting the property back to `False`.
+HAWQ hosts are configured during the installation process to use passwordless SSH for intra-cluster communications. Temporary password-based authentication must be enabled on each HAWQ host in preparation for this configuration.
+
+Password authentication is typically disabled by default in cloud images. Update the cloud configuration in `/etc/cloud/cloud.cfg` to enable password authentication in your AMI(s). Set `ssh_pwauth: True` in this configuration file. If desired, set the property back to `False` after HAWQ installation to disable password authentication.
##References<a id="topic_hgz_zwy_bv"></a>
http://git-wip-us.apache.org/repos/asf/incubator-hawq-docs/blob/df87f9ae/markdown/reference/sql/CREATE-DATABASE.html.md.erb
----------------------------------------------------------------------
diff --git a/markdown/reference/sql/CREATE-DATABASE.html.md.erb b/markdown/reference/sql/CREATE-DATABASE.html.md.erb
index 7ebab4e..1f5467e 100644
--- a/markdown/reference/sql/CREATE-DATABASE.html.md.erb
+++ b/markdown/reference/sql/CREATE-DATABASE.html.md.erb
@@ -25,20 +25,20 @@ where \<database\_attribute\> is:
The creator becomes the owner of the new database by default. Superusers can create databases owned by other users by using the `OWNER` clause. They can even create databases owned by users with no special privileges. Non-superusers with `CREATEDB` privilege can only create databases owned by themselves.
-By default, the new database will be created by cloning the standard system database `template1`. A different template can be specified by writing `TEMPLATE <template>`. In particular, by writing `TEMPLATE template0`, you can create a clean database containing only the standard objects predefined by HAWQ. This is useful if you wish to avoid copying any installation-local objects that may have been added to `template1`.
+By default, the new database is created by cloning the standard system database template named `template1`. You can specify a different template by including the `WITH TEMPLATE=<template>` clause in the `CREATE DATABASE` command. In particular, specifying `WITH TEMPLATE=template0` creates a clean database containing only the standard objects predefined by HAWQ. This is useful if you wish to avoid copying any installation-local objects that may have been added to `template1`.
## <a id="topic1__section4"></a>Parameters
<dt>\<database_name\></dt>
<dd>The name of a database to create.
-**Note:** HAWQ reserves the database name "hcatalog" for system use.</dd>
+**Note:** HAWQ reserves the database name `hcatalog` for system use.</dd>
<dt>OWNER=\<database_owner\> </dt>
<dd>The name of the database user who will own the new database, or `DEFAULT` to use the default owner (the user executing the command).</dd>
<dt>TEMPLATE=\<template\> </dt>
-<dd>The name of the template from which to create the new database, or `DEFAULT` to use the default template (*template1*).</dd>
+<dd>The name of the template from which to create the new database, or `DEFAULT` to use the default template (`template1`).</dd>
<dt>ENCODING=\<encoding\> </dt>
<dd>Character set encoding to use in the new database. Specify a string constant (such as `'SQL_ASCII'`), an integer encoding number, or `DEFAULT` to use the default encoding.</dd>
@@ -53,25 +53,25 @@ By default, the new database will be created by cloning the standard system data
`CREATE DATABASE` cannot be executed inside a transaction block.
-When you copy a database by specifying its name as the template, no other sessions can be connected to the template database while it is being copied. New connections to the template database are locked out until `CREATE DATABASE` completes.
+Only template databases may be specified in the `WITH TEMPLATE=<template>` clause. When you specify `template1` or `template0` as the database template, no other sessions can be connected to the template database during the create operation. New connections to the template database are locked out until `CREATE DATABASE` completes.
The `CONNECTION LIMIT` is not enforced against superusers.
## <a id="topic1__section6"></a>Examples
-To create a new database:
+To create a new database using the default template:
``` pre
CREATE DATABASE gpdb;
```
-To create a database `sales` owned by user `salesapp` with a default tablespace of `salesspace`:
+To create a database named `sales` owned by user `salesapp` with a default tablespace of `salesspace`:
``` pre
CREATE DATABASE sales OWNER=salesapp TABLESPACE=salesspace;
```
-To create a database `music` which supports the ISO-8859-1 character set:
+To create a database named `music` which supports the ISO-8859-1 character set:
``` pre
CREATE DATABASE music ENCODING='LATIN1';
[2/7] incubator-hawq-docs git commit: HAWQ-1252 - pljava doc cleanup,
working examples (closes #83)
Posted by yo...@apache.org.
HAWQ-1252 - pljava doc cleanup, working examples (closes #83)
Project: http://git-wip-us.apache.org/repos/asf/incubator-hawq-docs/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-hawq-docs/commit/04bf221c
Tree: http://git-wip-us.apache.org/repos/asf/incubator-hawq-docs/tree/04bf221c
Diff: http://git-wip-us.apache.org/repos/asf/incubator-hawq-docs/diff/04bf221c
Branch: refs/heads/release/2.1.0.0-incubating
Commit: 04bf221c21d105941951f3fefe6c0cffd860d9a2
Parents: 2c02642
Author: Lisa Owen <lo...@pivotal.io>
Authored: Wed Jan 11 16:06:21 2017 -0800
Committer: David Yozie <yo...@apache.org>
Committed: Wed Jan 11 16:06:21 2017 -0800
----------------------------------------------------------------------
markdown/plext/using_pljava.html.md.erb | 1146 +++++++++++++++++---------
1 file changed, 771 insertions(+), 375 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-hawq-docs/blob/04bf221c/markdown/plext/using_pljava.html.md.erb
----------------------------------------------------------------------
diff --git a/markdown/plext/using_pljava.html.md.erb b/markdown/plext/using_pljava.html.md.erb
index 99b5767..e463093 100644
--- a/markdown/plext/using_pljava.html.md.erb
+++ b/markdown/plext/using_pljava.html.md.erb
@@ -2,128 +2,143 @@
title: Using PL/Java
---
-This section contains an overview of the HAWQ PL/Java language.
+This section provides an overview of the HAWQ PL/Java procedural language extension.
## <a id="aboutpljava"></a>About PL/Java
-With the HAWQ PL/Java extension, you can write Java methods using your favorite Java IDE and install the JAR files that implement the methods in your HAWQ cluster.
+With the PL/Java extension, you can write Java methods using your favorite Java IDE and invoke the methods from PostgreSQL user-defined functions (UDFs).
-**Note**: If building HAWQ from source, you must specify PL/Java as a build option when compiling HAWQ. To use PL/Java in a HAWQ deployment, you must explicitly enable the PL/Java extension in all desired databases.
+The HAWQ PL/Java package is based on the open source PL/Java 1.4.0 and provides the following features:
-The HAWQ PL/Java package is based on the open source PL/Java 1.4.0. HAWQ PL/Java provides the following features.
-
-- Ability to execute PL/Java functions with Java 1.6 or 1.7.
-- Standardized utilities (modeled after the SQL 2003 proposal) to install and maintain Java code in the database.
-- Standardized mappings of parameters and result. Complex types as well as sets are supported.
-- An embedded, high performance, JDBC driver utilizing the internal HAWQ Database SPI routines.
+- PL/Java function execution with Java 1.6 or 1.7.
+- Standardized mappings of Java and PostgreSQL parameters and results. Complex types as well as sets are supported.
+- Two HAWQ Java languages:
+ - `java` - Tusted PL/Java language
+ - `javau` - Untrusted PL/Java language (superusers only)
+- An embedded, high performance, JDBC driver utilizing the internal HAWQ Database Server Programming Interface (SPI).
- Metadata support for the JDBC driver. Both `DatabaseMetaData` and `ResultSetMetaData` are included.
-- The ability to return a `ResultSet` from a query as an alternative to building a ResultSet row by row.
+- A standard way of passing parameters and return values. Complex types and sets are passed using the standard JDBC `ResultSet` class.
+- The ability to return a `ResultSet` from a query as an alternative to building a `ResultSet` row by row.
- Full support for savepoints and exception handling.
-- The ability to use IN, INOUT, and OUT parameters.
-- Two separate HAWQ languages:
- - pljava, TRUSTED PL/Java language
- - pljavau, UNTRUSTED PL/Java language
- Transaction and Savepoint listeners enabling code execution when a transaction or savepoint is committed or rolled back.
- Integration with GNU GCJ on selected platforms.
-A function in SQL will appoint a static method in a Java class. In order for the function to execute, the appointed class must available on the class path specified by the HAWQ server configuration parameter `pljava_classpath`. The PL/Java extension adds a set of functions that helps to install and maintain the Java classes. Classes are stored in normal Java archives, JAR files. A JAR file can optionally contain a deployment descriptor that in turn contains SQL commands to be executed when the JAR is deployed or undeployed. The functions are modeled after the standards proposed for SQL 2003.
-
-PL/Java implements a standard way of passing parameters and return values. Complex types and sets are passed using the standard JDBC ResultSet class.
-
-A JDBC driver is included in PL/Java. This driver calls HAWQ internal SPI routines. The driver is essential since it is common for functions to make calls back to the database to fetch data. When PL/Java functions fetch data, they must use the same transactional boundaries that are used by the main function that entered PL/Java execution context.
+PL/Java is optimized for performance. The Java virtual machine executes within the same process as the backend, minimizing call overhead. PL/Java brings the power of Java to the database itself, enabling the database-intensive business logic to execute as close to the actual data as possible.
-PL/Java is optimized for performance. The Java virtual machine executes within the same process as the backend to minimize call overhead. PL/Java is designed with the objective to enable the power of Java to the database itself so that database intensive business logic can execute as close to the actual data as possible.
-
-The standard Java Native Interface (JNI) is used when bridging calls between the backend and the Java VM.
+Calls between the HAWQ backend and the Java VM are bridged using the standard Java Native Interface (JNI).
## <a id="abouthawqpljava"></a>About HAWQ PL/Java
+PL/Java is embedded in your HAWQ product distribution or within your HAWQ build if you chose to enable it as a build option. To use PL/Java in a HAWQ deployment, you must explicitly enable the PL/Java extension in all desired databases.
+
There are a few key differences between the implementation of PL/Java in standard PostgreSQL and HAWQ.
-### <a id="pljavafunctions"></a>Functions
+### <a id="pljavafunctions"></a>Unsupported Functions
-The following functions are not supported in HAWQ. The classpath is handled differently in a distributed HAWQ environment than in the PostgreSQL environment.
+The following functions are not supported in HAWQ:
-- sqlj.install_jar
-- sqlj.install_jar
-- sqlj.replace_jar
-- sqlj.remove_jar
-- sqlj.get_classpath
-- sqlj.set_classpath
+- sqlj.install_jar()
+- sqlj.install_jar()
+- sqlj.replace_jar()
+- sqlj.remove_jar()
+- sqlj.get_classpath()
+- sqlj.set_classpath()
-HAWQ uses the `pljava_classpath` server configuration parameter in place of the `sqlj.set_classpath` function.
+The PL/Java classpath is handled differently in a distributed HAWQ environment than that of the PostgreSQL environment. HAWQ uses the `pljava_classpath` server configuration parameter in place of the `sqlj.set_classpath()` function.
### <a id="serverconfigparams"></a>Server Configuration Parameters
-PL/Java uses server configuration parameters to configure classpath, Java VM, and other options. Refer to the [Server Configuration Parameter Reference](../reference/HAWQSiteConfig.html) for general information about HAWQ server configuration parameters.
+PL/Java uses server configuration parameters to configure the classpath, Java Virtual Machine (VM), and other options. Refer to the [Server Configuration Parameter Reference](../reference/HAWQSiteConfig.html) for general information about HAWQ server configuration parameters.
-The following server configuration parameters are used by PL/Java in HAWQ. These parameters replace the `pljava.*` parameters that are used in the standard PostgreSQL PL/Java implementation.
+The following server configuration parameters are used by HAWQ PL/Java. These parameters replace the `pljava.*` configuration parameters that are used in the standard PostgreSQL PL/Java implementation.
#### pljava\_classpath
-A colon (:) separated list of the jar files containing the Java classes used in any PL/Java functions. The jar files must be installed in the same locations on all HAWQ hosts. With the trusted PL/Java language handler, jar file paths must be relative to the `$GPHOME/lib/postgresql/java/` directory. With the untrusted language handler (javaU language tag), paths may be relative to `$GPHOME/lib/postgresql/java/` or absolute.
+A PL/Java user-defined function will appoint a static method in a Java class. In order for the function to execute, the appointed class must be available on the class path specified by the [`pljava_classpath`](../reference/guc/parameter_definitions.html#pljava_classpath) HAWQ server configuration parameter.
-#### pljava\_statement\_cache\_size
-
-Sets the size in KB of the Most Recently Used (MRU) cache for prepared statements.
+`pljava_classpath` is a colon (:) separated list of the JAR files implementing the Java classes referenced in any PL/Java user-defined functions. The JAR files must be installed in the same locations on all HAWQ hosts. When using the trusted PL/Java language handler (`java` language tag), JAR file paths must be relative to the `$GPHOME/lib/postgresql/java/` directory. Relative or absolute JAR file paths may be used with the untrusted PL/Java language handler (`javau` language tag).
#### pljava\_release\_lingering\_savepoints
-If TRUE, lingering savepoints will be released on function exit. If FALSE, they will be rolled back.
+[`pljava_release_lingering_savepoints`](../reference/guc/parameter_definitions.html#pljava_release_lingering_savepoints) identifies the action to perform when lingering savepoints exist after a PL/Java user-defined function exits. If TRUE, lingering savepoints are released on function exit. If FALSE, lingering savepoints are rolled back.
+
+#### pljava\_statement\_cache\_size
+
+[`pljava_statement_cache_size`](../reference/guc/parameter_definitions.html#pljava_statement_cache_size) sets the size (in KB) of the Most Recently Used (MRU) cache for prepared statements.
#### pljava\_vmoptions
-Defines the start up options for the Java VM.
+[`pljava_vmoptions`](../reference/guc/parameter_definitions.html#pljava_vmoptions) defines the set of space-delimited start-up options for the Java VM.
### <a id="setting_serverconfigparams"></a>Setting PL/Java Configuration Parameters
-You can set PL/Java server configuration parameters at the session level, or globally across your whole cluster. Your HAWQ cluster configuration must be reloaded after setting a server configuration value globally.
+You can set PL/Java server configuration parameters at the session level, or globally across your whole cluster.
#### <a id="setsrvrcfg_global"></a>Cluster Level
You will perform different procedures to set a PL/Java server configuration parameter for your whole HAWQ cluster depending upon whether you manage your cluster from the command line or use Ambari. If you use Ambari to manage your HAWQ cluster, you must ensure that you update server configuration parameters only via the Ambari Web UI. If you manage your HAWQ cluster from the command line, you will use the `hawq config` command line utility to set PL/Java server configuration parameters.
+You must reload your HAWQ cluster configuration after you set a server configuration value globally.
+
The following examples add a JAR file named `myclasses.jar` to the `pljava_classpath` server configuration parameter for the entire HAWQ cluster.
If you use Ambari to manage your HAWQ cluster:
-1. Set the `pljava_classpath` configuration property to include `myclasses.jar` via the HAWQ service **Configs > Advanced > Custom hawq-site** drop down.
+1. Set the `pljava_classpath` configuration property **Key** **Value** to include `myclasses.jar` via the HAWQ service **Configs > Advanced > Custom hawq-site** drop down.
2. Select **Service Actions > Restart All** to load the updated configuration.
If you manage your HAWQ cluster from the command line:
-1. Log in to the HAWQ master host as a HAWQ administrator and source the file `/usr/local/hawq/greenplum_path.sh`.
+1. Log in to the HAWQ master node as a HAWQ administrator and set up your HAWQ environment.
``` shell
- $ source /usr/local/hawq/greenplum_path.sh
+ $ ssh gpadmin@master
+ gpadmin@master$ source /usr/local/hawq/greenplum_path.sh
```
1. Use the `hawq config` utility to set `pljava_classpath`:
``` shell
- $ hawq config -c pljava_classpath -v \'myclasses.jar\'
+ gpadmin@master$ hawq config -c pljava_classpath -v 'myclasses.jar'
```
+
+ The `-c` option identifies the name of the server configuration parameter, `-v` identifies the new value.
+
2. Reload the HAWQ configuration:
``` shell
- $ hawq stop cluster -u
+ gpadmin@master$ hawq stop cluster -u
```
#### <a id="setsrvrcfg_session"></a>Session Level
-To set a PL/Java server configuration parameter for only the *current* database session, set the parameter within the `psql` subsystem. For example, to set `pljava_classpath`:
+To set a PL/Java server configuration parameter only for the *current* database session, set the parameter within the `psql` subsystem. For example:
``` sql
=> SET pljava_classpath='myclasses.jar';
```
+## <a id="security"></a>Security
-## <a id="enablepljava"></a>Enabling and Removing PL/Java Support
+This section describes specific security considerations when using PL/Java.
+
+### <a id="installation"></a>Enable/Disable
+
+Only a database superuser can enable and disable PL/Java. You install the PL/Java utility functions with `SECURITY DEFINER` privileges; they execute with the access permissions granted to the creator of the functions.
+
+### <a id="trustedlang"></a>Trusted/Untrusted Language
-The PL/Java extension must be explicitly enabled on each database in which it will be used.
+PL/Java includes trusted and untrusted language variants.
+The PL/Java trusted language is named `java`. The trusted PL/Java language cannot access the file system as stipulated by the PostgreSQL definition of a trusted language. Any database user can create and execute functions in the trusted `java` language.
+
+The PL/Java untrusted language is named `javau`. You cannot restrict operation of functions you create with the `javau` untrusted language. Only database superusers have privileges to create untrusted PL/Java UDFs. Only database superusers and other database users explicitly `GRANT`ed the permissions can execute untrusted PL/Java UDFs.
+
+## <a id="enablepljava"></a>Enabling and Removing PL/Java Support
+
+The PL/Java extension must be explicitly enabled on each database in which it will be used. To remove PL/Java support, the extension must be explicitly removed from each database in which it was used.
### <a id="pljavaprereq"></a>Prerequisites
@@ -131,76 +146,79 @@ Before you enable PL/Java:
1. Ensure that you have installed a supported Java runtime environment and that the `$JAVA_HOME` variable is set to the same path on the master and all segment nodes.
-2. Perform the following step on all machines to set up `ldconfig` for the installed JDK:
+2. Perform the following step on all nodes in your HAWQ cluster to set up the shared library cache for the installed JDK:
``` shell
- $ echo "$JAVA_HOME/jre/lib/amd64/server" > /etc/ld.so.conf.d/libjdk.conf
- $ ldconfig
+ root@hawq-node$ echo "$JAVA_HOME/jre/lib/amd64/server" > /etc/ld.so.conf.d/libjdk.conf
+ root@hawq-node$ ldconfig
```
4. Make sure that your HAWQ cluster is running, you have sourced `greenplum_path.sh` and that your `$GPHOME` environment variable is set.
+ ``` shell
+ $ ssh gpadmin@master
+ gpadmin@master$ source /usr/local/hawq/greenplum_path.sh
+ gpadmin@master$ hawq state
+ ```
### <a id="enablepljava"></a>Enable PL/Java and Install JAR Files
-To use PL/Java:
+To use PL/Java, you must:
1. Enable the language for each database.
-1. Install user-created JAR files on all HAWQ hosts.
-1. Add the names of the JAR files to the HAWQ `pljava_classpath` server configuration parameter. This parameter value should identify a list of the installed JAR files.
+1. Install custom JAR files on all HAWQ hosts.
+1. Add the names of the JAR files to the HAWQ `pljava_classpath` server configuration parameter. This parameter value should identify a list of all installed JAR files.
#### <a id="enablepljava"></a>Enable PL/Java and Install JAR Files
Perform the following steps as the `gpadmin` user:
-1. Enable PL/Java by running the `$GPHOME/share/postgresql/pljava/install.sql` SQL script in the databases that will use PL/Java. The `install.sql` script registers both the trusted and untrusted PL/Java languages. For example, the following command enables PL/Java on a database named `testdb`:
+1. Enable PL/Java by running the `$GPHOME/share/postgresql/pljava/install.sql` SQL script on the databases that will use PL/Java. The `install.sql` script registers both the trusted (`java`) and untrusted PL/Java (`javau`) languages. For example, the following command enables the PL/Java languages on a database named `testdb`:
``` shell
- $ psql -d testdb -f $GPHOME/share/postgresql/pljava/install.sql
+ gpadmin@master$ psql -d testdb -f $GPHOME/share/postgresql/pljava/install.sql
```
To enable the PL/Java extension in all new HAWQ databases, run the script on the `template1` database:
``` shell
- $ psql -d template1 -f $GPHOME/share/postgresql/pljava/install.sql
+ gpadmin@master$ psql -d template1 -f $GPHOME/share/postgresql/pljava/install.sql
```
- Use this option *only* if you are certain you want to enable PL/Java in all new databases.
+ Use this option *only* if you are certain you want to enable both the trusted and untrusted versions of PL/Java in all newly-created databases. After you invoke this command, PL/Java will be enabled in each new database that a user creates.
-2. Copy your Java archives (JAR files) to `$GPHOME/lib/postgresql/java/` on all HAWQ hosts. This example uses the `hawq scp` utility to copy the `myclasses.jar` file located in the current directory:
+2. Copy all custom Java JAR files to `$GPHOME/lib/postgresql/java/` on all HAWQ nodes. This example uses the `hawq scp` utility to copy the `myclasses.jar` file to the list of hosts identified in the `hawq_hosts` file:
``` shell
- $ hawq scp -f hawq_hosts myclasses.jar =:$GPHOME/lib/postgresql/java/
+ gpadmin@master$ hawq scp -f ./hawq_hosts ./myclasses.jar =:/usr/local/hawq/lib/postgresql/java/
```
- The `hawq_hosts` file contains a list of the HAWQ hosts.
-3. Add the JAR files to the `pljava_classpath` configuration parameter. Refer to [Setting PL/Java Configuration Parameters](#setting_serverconfigparams) for the specific procedure.
+3. Add all JAR files to the `pljava_classpath` configuration parameter. Refer to [Setting PL/Java Configuration Parameters](#setting_serverconfigparams) for the specific procedure.
-5. (Optional) Your HAWQ installation includes an `examples.sql` file. This script contains sample PL/Java functions that you can use for testing. Run the commands in this file to create and run test functions that use the Java classes in `examples.jar`:
+5. (Optional) Your HAWQ installation includes an `examples.sql` file. This script registers sample PL/Java functions that you can use to test the PL/Java installation. Run the commands in this file to create and execute PL/Java user-defined functions that use the Java classes implemented in `examples.jar`. The database on which you run the script must have PL/Java enabled:
``` shell
- $ psql -f $GPHOME/share/postgresql/pljava/examples.sql
+ gpadmin@master$ psql -d <dbname> -f $GPHOME/share/postgresql/pljava/examples.sql
```
#### Configuring PL/Java VM Options
-PL/Java JVM options can be configured via the `pljava_vmoptions` server configuration parameter. For example, `pljava_vmoptions=-Xmx512M` sets the maximum heap size of the JVM. The default `-Xmx` value is `64M`.
+Configure PL/Java JVM options via the `pljava_vmoptions` server configuration parameter. For example, `pljava_vmoptions=-Xmx512M` sets the maximum heap size of the JVM. (The default `-Xmx` value is `64M`.)
Refer to [Setting PL/Java Configuration Parameters](#setting_serverconfigparams) for the specific procedure to set PL/Java server configuration parameters.
-
### <a id="uninstallpljava"></a>Disable PL/Java
To disable PL/Java, you should:
1. Remove PL/Java support from each database in which it was added.
-2. Uninstall the Java JAR files.
+2. Uninstall/remove the Java JAR files.
#### <a id="uninstallpljavasupport"></a>Remove PL/Java Support from Databases
-For a database that no longer requires the PL/Java language, remove support for PL/Java by running the `uninstall.sql` script as the `gpadmin` user. For example, the following command disables the PL/Java language in the specified database:
+Remove support for PL/Java by running the `uninstall.sql` script as the `gpadmin` user. For example, the following command disables the PL/Java languages in the specified database:
``` shell
-$ psql -d <dbname> -f $GPHOME/share/postgresql/pljava/uninstall.sql
+gpadmin@master$ psql -d <dbname> -f $GPHOME/share/postgresql/pljava/uninstall.sql
```
Replace \<dbname\> with the name of the target database.
@@ -214,7 +232,7 @@ If you use Ambari to manage your cluster:
1. Remove the `pljava_classpath` configuration property via the HAWQ service **Configs > Advanced > Custom hawq-site** drop down.
-2. Remove the JAR files from the `$GPHOME/lib/postgresql/java/` directory of each HAWQ host.
+2. Remove the JAR files from the `$GPHOME/lib/postgresql/java/` directory on each HAWQ host.
3. Select **Service Actions > Restart All** to restart your HAWQ cluster.
@@ -224,21 +242,21 @@ If you manage your cluster from the command line:
1. Log in to the HAWQ master host as a HAWQ administrator and source the file `/usr/local/hawq/greenplum_path.sh`.
``` shell
- $ source /usr/local/hawq/greenplum_path.sh
+ gpadmin@master$ source /usr/local/hawq/greenplum_path.sh
```
-1. Use the `hawq config` utility to remove `pljava_classpath`:
+1. Use the `hawq config` utility to remove the `pljava_classpath` setting:
``` shell
- $ hawq config -r pljava_classpath
+ gpadmin@master$ hawq config -r pljava_classpath
```
-2. Remove the JAR files from the `$GPHOME/lib/postgresql/java/` directory of each HAWQ host.
+2. Remove the JAR files from the `$GPHOME/lib/postgresql/java/` directory on each HAWQ host.
-3. If you manage your cluster from the command line, run:
+3. Restart your HAWQ cluster:
``` shell
- $ hawq restart cluster
+ gpadmin@master$ hawq restart cluster
```
@@ -255,33 +273,48 @@ This section provides information about writing functions with PL/Java.
- [Returning a SETOF \<scalar type\>](#returnsetofscalar)
- [Returning a SETOF \<complex type\>](#returnsetofcomplex)
+You must have a Java SDK installed to write Java methods.
### <a id="sqldeclaration"></a>SQL Declaration
+PL/Java functions are defined using the standard SQL [CREATE FUNCTION](../reference/sql/CREATE-FUNCTION.html) syntax. A PL/Java user-defined function specifies `LANGUAGE java` (trusted) or `LANGUAGE javau` (untrusted). The function must also include `RETURNS` and `AS` clauses.
-A Java function is declared with the name of a class and a static method on that class. The class will be resolved using the classpath that has been defined for the schema where the function is declared. If no classpath has been defined for that schema, the public schema is used. If no classpath is found there either, the class is resolved using the system classloader.
+The `RETURNS` clause identifies the type of the function's return value.
-The following function can be declared to access the static method getProperty on `java.lang.System` class:
+Use the `AS` clause to identify the name of a Java class and the static method on that class that HAWQ invokes when it executes the function. Any arguments to the UDF are also passed to the Java method.
-```sql
-=> CREATE FUNCTION getsysprop(VARCHAR)
+The Java class is resolved using the classpath that has been defined for the schema in which the function is declared. If no classpath has been defined for that schema, the `public` schema is used. If no classpath is found in the `public` schema, an attempt is made to resolve the class using the Java system class loader.
+
+Example: Create a UDF wrapper around the `java.lang.System` class `getProperty()` method. `getProperty()` takes a property name string as input and returns the string value of that property:
+
+``` sql
+=> CREATE FUNCTION getsysprop_udf(VARCHAR)
RETURNS VARCHAR
AS 'java.lang.System.getProperty'
LANGUAGE java;
```
-Run the following command to return the Java `user.home` property:
+Execute the `getsysprop_udf()` PL/Java UDF, passing as input the property name `user.home`:
```sql
-=> SELECT getsysprop('user.home');
+=> SELECT getsysprop_udf('user.home');
```
+```
+ getsysprop_udf
+----------------
+ /home/gpadmin
+(1 row)
+```
+
+`getsyprop_udf()` returns the value `/home/gpadmin`, the current user's (`gpadmin`) home directory.
+
### <a id="typemapping"></a>Type Mapping
-Scalar types are mapped in a straightforward way. This table lists the current mappings.
+Scalar types are mapped between HAWQ and Java in a straightforward way. The current HAWQ to Java type mappings are listed in the table below.
-***Table 1: PL/Java data type mappings***
+**Table 1: PL/Java Data Type Mappings**
-| PostgreSQL | Java |
+| HAWQ | Java |
|------------|------|
| bool | boolean |
| char | byte |
@@ -299,306 +332,629 @@ Scalar types are mapped in a straightforward way. This table lists the current m
| complex | java.sql.ResultSet |
| setof complex | java.sql.ResultSet |
-All other types are mapped to `java.lang.String` and will utilize the standard textin/textout routines registered for respective type.
+All other types are mapped to `java.lang.String` and will utilize the standard textin/textout routines registered for the respective type.
### <a id="nullhandling"></a>NULL Handling
-The scalar types that map to Java primitives can not be passed as NULL values. To pass NULL values, those types can have an alternative mapping. You enable this mapping by explicitly denoting it in the method reference.
+The scalar types that map to Java primitives can not be passed as NULL values to Java methods. To pass NULL values, those types should be mapped to the Java object wrapper class that corresponds with the primitive, and must be explicitly denoted in the method reference. For example, the object wrapper class for the `integer` primitive type is `java.lang.Integer`.
-```sql
-=> CREATE FUNCTION trueIfEvenOrNull(integer)
- RETURNS bool
- AS 'foo.fee.Fum.trueIfEvenOrNull(java.lang.Integer)'
- LANGUAGE java;
-```
+Example: Handling Null Inputs
-The Java code would be similar to this:
-
-```java
-package foo.fee;
-public class Fum
-{
- static boolean trueIfEvenOrNull(Integer value)
- {
- return (value == null)
- ? true
- : (value.intValue() % 1) == 0;
- }
-}
-```
+1. Create a work area for the example:
-The following two statements both yield true:
+ ``` shell
+ $ mkdir pljava_work
+ $ cd pljava_work
+ $ export PLJAVAWORK=`pwd`
+ $ mkdir -p pljex/foo/fee
+ $ cd pljex/foo/fee
+ ```
-```sql
-=> SELECT trueIfEvenOrNull(NULL);
-=> SELECT trueIfEvenOrNull(4);
-```
+2. Create a new file named `Fum.java`, adding the following text to create a class named `Fum` with a single method named `trueIfEvenOrNull()`. This method takes an integer as input and returns true if the integer is even or NULL, false otherwise:
-In order to return NULL values from a Java method, you use the object type that corresponds to the primitive (for example, you return `java.lang.Integer` instead of `int`). The PL/Java resolve mechanism finds the method regardless. Since Java cannot have different return types for methods with the same name, this does not introduce any ambiguity.
+ ``` shell
+ $ vi Fum.java
+ ```
+
+ ``` java
+ package foo.fee;
+ public class Fum
+ {
+ static boolean trueIfEvenOrNull(Integer value)
+ {
+ return (value == null)
+ ? true
+ : (value.intValue() % 1) == 0;
+ }
+ }
+ ```
+
+3. Compile the `Fum` class and create a JAR file for this class named `pljex.jar`:
+
+ ``` shell
+ $ javac Fum.java
+ $ cd ../..
+ $ jar cf pljex.jar foo
+ ```
+
+4. Copy the JAR file to the default PL/Java classpath directory:
+
+ ``` shell
+ $ cp pljex.jar /usr/local/hawq/lib/postgresql/java/
+ ```
+
+5. Start the `psql` subsystem:
+
+ ``` shell
+ $ psql -d testdb
+ ```
+
+6. Add the JAR file to the session-level classpath:
+
+ ``` sql
+ => SET pljava_classpath='pljex.jar';
+ ```
+
+7. Create a trusted PL/Java UDF that invokes the `Fum` class `trueIfEvenOrNull()` method:
+
+ ``` sql
+ => CREATE FUNCTION isEvenOrNull(integer)
+ RETURNS bool
+ AS 'foo.fee.Fum.trueIfEvenOrNull(java.lang.Integer)'
+ LANGUAGE java;
+ ```
+
+ Notice that the UDF input type is `integer`, while the `trueIfEvenOrNull()` Java method input is a `java.lang.Integer` object.
+
+8. Execute the UDF twice, once with a NULL and once with an even input:
+
+ ``` sql
+ => SELECT isEvenOrNull(NULL);
+ => SELECT isEvenOrNull(4);
+ ```
+
+ Both statements should return true.
### <a id="complextypes"></a>Complex Types
-A complex type will always be passed as a read-only `java.sql.ResultSet` with exactly one row. The `ResultSet` is positioned on its row so a call to `next()` should not be made. The values of the complex type are retrieved using the standard getter methods of the `ResultSet`.
+PL/Java supports complex types. Use the `CREATE TYPE` SQL command to create the complex type. Use the `CREATE FUNCTION` SQL command to define a PL/Java UDF whose input argument is the new (complex) type.
-Example:
+A complex type is always passed to a Java method as a read-only `java.sql.ResultSet` with exactly one row. The values of the specific fields in the complex type are retrieved using the standard getter method associated with the data type of each field present in the `ResultSet`.
-```sql
-=> CREATE TYPE complexTest
- AS(base integer, incbase integer, ctime timestamptz);
-=> CREATE FUNCTION useComplexTest(complexTest)
- RETURNS VARCHAR
- AS 'foo.fee.Fum.useComplexTest'
- IMMUTABLE LANGUAGE java;
-```
+Example: Complex Input Types
-In the Java class `Fum`, we add the following static method:
-
-```java
-public static String useComplexTest(ResultSet complexTest)
-throws SQLException
-{
- int base = complexTest.getInt(1);
- int incbase = complexTest.getInt(2);
- Timestamp ctime = complexTest.getTimestamp(3);
- return "Base = \"" + base +
- "\", incbase = \"" + incbase +
- "\", ctime = \"" + ctime + "\"";
-}
-```
+1. Add the following definitions and static method to the Java `Fum` class you created in an earlier exercise. This method outputs the components of the complex type comprised of two integer fields and a timestamp field:
+
+ ``` shell
+ $ cd $PLJAVAWORK/pljex/foo/fee
+ $ vi Fum.java
+ ```
+
+ ``` java
+ import java.sql.ResultSet;
+ import java.sql.SQLException;
+ import java.sql.Timestamp;
+ ```
+
+ ``` java
+ public static String useComplexTest(ResultSet complexTest)
+ throws SQLException
+ {
+ int base = complexTest.getInt(1);
+ int incbase = complexTest.getInt(2);
+ Timestamp ctime = complexTest.getTimestamp(3);
+ return "Base = \"" + base +
+ "\", incbase = \"" + incbase +
+ "\", ctime = \"" + ctime + "\"";
+ }
+ ```
+
+ Add the `imports` under the `package` definition. And be sure to include the `useComplexTypes()` method within the `Fum` class `{}`s.
+
+ Notice that the `ResultSet` object is immediately referenced; a call to `next()` is not required.
+
+2. Compile the `Fum` class, create the JAR file, and copy the JAR file to the default PL/Java classpath directory:
+
+ ``` shell
+ $ javac Fum.java
+ $ cd ../..
+ $ jar cf pljex.jar foo
+ $ cp pljex.jar /usr/local/hawq/lib/postgresql/java/
+ ```
+
+5. Start the `psql` subsystem:
+
+ ``` shell
+ $ psql -d testdb
+ ```
+
+6. Add the JAR file to the session-level classpath:
+
+ ``` sql
+ => SET pljava_classpath='pljex.jar';
+ ```
+
+7. Create a custom complex type with 2 integers and a single timestamp component:
+
+ ``` sql
+ => CREATE TYPE complexTestType
+ AS(base integer, incbase integer, ctime timestamptz);
+ ```
+
+7. Create a PL/Java UDF that invokes the `Fum` class `useComplexTest()` method, specifying a `complexTestType` as input:
+
+ ```sql
+ => CREATE FUNCTION useComplexTest(complexTestType)
+ RETURNS VARCHAR
+ AS 'foo.fee.Fum.useComplexTest'
+ IMMUTABLE LANGUAGE java;
+ ```
+
+8. Execute the `useComplexTest()` UDF, providing 2 integers and a timestamp as input:
+
+ ```sql
+ => SELECT useComplexTest( '(1,2,20170101010203)' );
+ ```
+
+ ```
+ usecomplextest
+ ------------------------------------------------------------
+ Base = "1", incbase = "2", ctime = "2017-01-01 01:02:03.0"
+ (1 row)
+ ```
+
+ Running the UDF displays the fields and values comprising the complex type.
### <a id="returningcomplextypes"></a>Returning Complex Types
-Java does not stipulate any way to create a `ResultSet`. Hence, returning a ResultSet is not an option. The SQL-2003 draft suggests that a complex return value should be handled as an IN/OUT parameter. PL/Java implements a `ResultSet` that way. If you declare a function that returns a complex type, you will need to use a Java method with boolean return type with a last parameter of type `java.sql.ResultSet`. The parameter will be initialized to an empty updateable ResultSet that contains exactly one row.
+As Java does not define any way to create a `ResultSet`, returning a `ResultSet` from a Java method is not an option. The SQL-2003 draft suggests that a complex return value should be handled as an input/output argument. Conveniently, PL/Java implements a `ResultSet` that way. To declare a function that returns a complex type, you must implement a Java method with a `boolean` return type and a last input argument of type `java.sql.ResultSet`. This input/output argument will be initialized to an empty updateable `ResultSet` that contains exactly one row.
-Assume that the complexTest type in previous section has been created.
+Example: Complex Return Types
-```sql
-=> CREATE FUNCTION createComplexTest(int, int)
- RETURNS complexTest
- AS 'foo.fee.Fum.createComplexTest'
- IMMUTABLE LANGUAGE java;
-```
+1. Create the `complexTestType` type definition if you did not yet create it:
-The PL/Java method resolve will now find the following method in the `Fum` class:
-
-```java
-public static boolean complexReturn(int base, int increment,
- ResultSet receiver)
-throws SQLException
-{
- receiver.updateInt(1, base);
- receiver.updateInt(2, base + increment);
- receiver.updateTimestamp(3, new
- Timestamp(System.currentTimeMillis()));
- return true;
-}
-```
+ ``` sql
+ => CREATE TYPE complexTestType
+ AS(base integer, incbase integer, ctime timestamptz);
+ ```
+
+2. Add the following static method to the Java class `Fum` you created in an earlier exercise. This method takes two integers as input, returning a complex type consisting of the first input integer, first input integer added to the second input integer, and a current timestamp:
+
+ ``` shell
+ $ cd $PLJAVAWORK/pljex/foo/fee
+ $ vi Fum.java
+ ```
+
+ ``` java
+ public static boolean complexReturn(int base, int increment,
+ ResultSet receiver)
+ throws SQLException
+ {
+ receiver.updateInt(1, base);
+ receiver.updateInt(2, base + increment);
+ receiver.updateTimestamp(3, new
+ Timestamp(System.currentTimeMillis()));
+ return true;
+ }
+ ```
+
+ The return value denotes if the `receiver` should be considered as a valid tuple (true) or NULL (false).
-The return value denotes if the receiver should be considered as a valid tuple (true) or NULL (false).
+3. Compile the `Fum` class, create the JAR file, and copy the JAR file to the default PL/Java classpath directory:
-### <a id="functionreturnsets"></a>Functions that Return Sets
+ ``` shell
+ $ javac Fum.java
+ $ cd ../..
+ $ jar cf pljex.jar foo
+ $ cp pljex.jar /usr/local/hawq/lib/postgresql/java/
+ ```
+
+5. Start the `psql` subsystem:
-When returning result set, you should not build a result set before returning it, because building a large result set would consume a large amount of resources. It is better to produce one row at a time. Incidentally, that is what the HAWQ backend expects a function with SETOF return to do. You can return a SETOF a scalar type such as an int, float or varchar, or you can return a SETOF a complex type.
+ ``` shell
+ $ psql -d testdb
+ ```
-### <a id="returnsetofscalar"></a>Returning a SETOF \<scalar type\>
+6. Add the JAR file to the session-level classpath:
-In order to return a set of a scalar type, you need create a Java method that returns something that implements the `java.util.Iterator` interface. Here is an example of a method that returns a SETOF varchar:
+ ``` sql
+ => SET pljava_classpath='pljex.jar';
+ ```
-```sql
-=> CREATE FUNCTION javatest.getSystemProperties()
- RETURNS SETOF varchar
- AS 'foo.fee.Bar.getNames'
- IMMUTABLE LANGUAGE java;
-```
+7. Create a PL/Java UDF that invokes the `Fum` class `createComplexType()` method, taking two integers as input:
-This simple Java method returns an iterator:
+ ```sql
+ => CREATE FUNCTION createComplexTest(int, int)
+ RETURNS complexTestType
+ AS 'foo.fee.Fum.complexReturn'
+ IMMUTABLE LANGUAGE java;
+ ```
+
+8. Execute the `createComplexTest()` UDF, providing 2 integers as input:
+
+ ```sql
+ => SELECT createComplexTest(11,22);
+ ```
+
+ ```
+ createcomplextest
+ --------------------------------------
+ (11,33,"2016-12-31 23:04:09.388-08")
+ (1 row)
+ ```
+
+ As described, the UDF, when executed, returns the first input integer, the sum of the first and second input integers, and the current timestamp.
+
+### <a id="functionreturnsets"></a>Functions that Return Sets
+
+PL/Java supports user-defined functions that return sets of both scalar and complex types.
+
+The HAWQ backend expects a function that `RETURNS` a `SETOF` to return one row at a time.
+
+### <a id="returnsetofscalar"></a>Returning a SETOF <Scalar Type>
+
+To return a set of a scalar type, the Java method must return an object that implements the `java.util.Iterator` interface.
+
+Example: Function that returns a SETOF varchar:
+
+1. Create a new file named `Bar.java`, adding the following text to create a class named `Bar` with a single method named `getNames()`. This method uses an `Iterator` to collect and return a list of string names:
+
+ ``` shell
+ $ cd $PLJAVAWORK/pljex/foo/fee
+ $ vi Bar.java
+ ```
-```java
-package foo.fee;
-import java.util.Iterator;
+ ``` java
+ package foo.fee;
+ import java.util.ArrayList;
+ import java.util.Iterator;
-public class Bar
-{
- public static Iterator getNames()
+ public class Bar
{
- ArrayList names = new ArrayList();
- names.add("Lisa");
- names.add("Bob");
- names.add("Bill");
- names.add("Sally");
- return names.iterator();
+ public static Iterator getNames()
+ {
+ ArrayList names = new ArrayList();
+ names.add("Lisa");
+ names.add("Bob");
+ names.add("Bill");
+ names.add("Sally");
+ return names.iterator();
+ }
}
-}
-```
+ ```
+
+
+3. Compile the `Bar` class (ignore warnings), create the JAR file, and copy the JAR file to the default PL/Java classpath directory:
+
+ ``` shell
+ $ javac *.java
+ $ cd ../..
+ $ jar cf pljex.jar foo
+ $ cp pljex.jar /usr/local/hawq/lib/postgresql/java/
+ ```
+
+5. Start the `psql` subsystem:
-### <a id="returnsetofcomplex"></a>Returning a SETOF \<complex type\>
+ ``` shell
+ $ psql -d testdb
+ ```
-A method returning a SETOF <complex type> must use either the interface `org.postgresql.pljava.ResultSetProvider` or `org.postgresql.pljava.ResultSetHandle`. The reason for having two interfaces is that they cater for optimal handling of two distinct use cases. The former is for cases when you want to dynamically create each row that is to be returned from the SETOF function. The latter makes is in cases where you want to return the result of an executed query.
+6. Add the JAR file to the session-level classpath:
+
+ ``` sql
+ => SET pljava_classpath='pljex.jar';
+ ```
+
+7. Create a PL/Java UDF that invokes the `Bar` class `getNames()` method:
+
+ ``` sql
+ => CREATE FUNCTION getListOfNames()
+ RETURNS SETOF varchar
+ AS 'foo.fee.Bar.getNames'
+ IMMUTABLE LANGUAGE java;
+ ```
+
+8. Execute the UDF:
+
+ ``` sql
+ => SELECT getListOfNames();
+ ```
+
+ ```
+ getlistofnames
+ ----------------
+ Lisa
+ Bob
+ Bill
+ Sally
+ (4 rows)
+ ```
+
+ The UDF returns a list of four string names.
+
+### <a id="returnsetofcomplex"></a>Returning a SETOF <Complex Type>
+
+A method returning a set of \<complex type\> must implement either the `org.postgresql.pljava.ResultSetProvider` interface or the `org.postgresql.pljava.ResultSetHandle` interface. The interfaces provide optimal handling for distinct use cases. Use `org.postgresql.pljava.ResultSetProvider` when you want to dynamically create each row the function returns. Use `org.postgresql.pljava.ResultSetHandle` in cases where you want to return the result of an executed query.
#### Using the ResultSetProvider Interface
-This interface has two methods. The boolean `assignRowValues(java.sql.ResultSet tupleBuilder, int rowNumber)` and the `void close()` method. The HAWQ query evaluator will call the `assignRowValues` repeatedly until it returns false or until the evaluator decides that it does not need any more rows. Then it calls close.
+The `ResultSetProvider` interface has two methods:
-You can use this interface the following way:
+- `boolean assignRowValues(java.sql.ResultSet tupleBuilder, int rowNumber)`
+- `void close()`
-```sql
-=> CREATE FUNCTION javatest.listComplexTests(int, int)
- RETURNS SETOF complexTest
- AS 'foo.fee.Fum.listComplexTest'
- IMMUTABLE LANGUAGE java;
-```
+The HAWQ query evaluator calls `assignRowValues()` repeatedly until it returns false or until the evaluator decides that it does not need any more rows. At that point, it will call `close()`.
+
+Example: Using the `ResultSetProvider` Interface
+
+1. Create a new file named `FumSetOfComplex.java`, adding the following text to create a class named `FumSetOfComplex` that implements the `ResultSetProvider` interface:
+
+ ``` shell
+ $ cd $PLJAVAWORK/pljex/foo/fee
+ $ vi FumSetOfComplex.java
+ ```
+
+ ``` java
+ package foo.fee;
+ import java.sql.ResultSet;
+ import java.sql.SQLException;
+ import java.sql.Timestamp;
+ import org.postgresql.pljava.ResultSetProvider;
-The function maps to a static java method that returns an instance that implements the `ResultSetProvider` interface.
-
-```java
-public class Fum implements ResultSetProvider
-{
- private final int m_base;
- private final int m_increment;
- public Fum(int base, int increment)
- {
- m_base = base;
- m_increment = increment;
- }
- public boolean assignRowValues(ResultSet receiver, int
+ public class FumSetOfComplex implements ResultSetProvider
+ {
+ private final int m_base;
+ private final int m_increment;
+ public FumSetOfComplex(int base, int increment)
+ {
+ m_base = base;
+ m_increment = increment;
+ }
+ public boolean assignRowValues(ResultSet receiver, int
currentRow)
- throws SQLException
- {
- // Stop when we reach 12 rows.
- //
- if(currentRow >= 12)
- return false;
- receiver.updateInt(1, m_base);
- receiver.updateInt(2, m_base + m_increment * currentRow);
- receiver.updateTimestamp(3, new
+ throws SQLException
+ {
+ if(currentRow >= 12)
+ return false;
+ receiver.updateInt(1, m_base);
+ receiver.updateInt(2, m_base + m_increment * currentRow);
+ receiver.updateTimestamp(3, new
Timestamp(System.currentTimeMillis()));
- return true;
- }
- public void close()
- {
- // Nothing needed in this example
- }
- public static ResultSetProvider listComplexTests(int base,
+ return true;
+ }
+ public void close()
+ {
+ /* Nothing needed in this example */
+ }
+ public static ResultSetProvider listComplex(int base,
int increment)
- throws SQLException
- {
- return new Fum(base, increment);
- }
-}
-```
+ throws SQLException
+ {
+ return new FumSetOfComplex(base, increment);
+ }
+ }
+ ```
+
+ The `listComplex()` method is called once. It will return NULL if no results are available. If results are available, `listComplex()` will return an instance of a `ResultSetProvider` interface. The Java class `FumSetOfComplex` implements this interface to return an instance of itself. `assignRowValues()` is called repeatedly until it returns false. At that time, `close()` is called.
+
+3. Compile the `FumSetOfComplex` class, create the JAR file, and copy the JAR file to the default PL/Java classpath directory:
+
+ ``` shell
+ $ javac -classpath /usr/local/hawq/lib/postgresql/pljava.jar FumSetOfComplex.java
+ $ cd ../..
+ $ jar cf pljex.jar foo
+ $ cp pljex.jar /usr/local/hawq/lib/postgresql/java/
+ ```
+
+5. Start the `psql` subsystem:
+
+ ``` shell
+ $ psql -d testdb
+ ```
+
+6. Add the JAR file to the session-level classpath:
+
+ ``` sql
+ => SET pljava_classpath='pljex.jar';
+ ```
+
+7. Create a PL/Java UDF that invokes the `FumSetOfComplex` class `listComplexTest()` method:
+
+ ```sql
+ => CREATE FUNCTION listComplexTest(int, int)
+ RETURNS SETOF complexTestType
+ AS 'foo.fee.FumSetOfComplex.listComplex'
+ IMMUTABLE LANGUAGE java;
+ ```
+
+8. Execute the UDF:
+
+ ``` sql
+ => SELECT listComplexTest(1,2);
+ ```
+
+ ```
+ testdb=# SELECT listComplexTest(1,2);
+ listcomplextest
+ -------------------------------------
+ (1,1,"2017-01-01 01:20:32.888-08")
+ (1,3,"2017-01-01 01:20:32.888-08")
+ (1,5,"2017-01-01 01:20:32.888-08")
+ (1,7,"2017-01-01 01:20:32.888-08")
+ (1,9,"2017-01-01 01:20:32.888-08")
+ ...
+ (12 rows)
+ ```
-The `listComplextTests` method is called once. It may return NULL if no results are available or an instance of the `ResultSetProvider`. Here the Java class `Fum` implements this interface so it returns an instance of itself. The method `assignRowValues` will then be called repeatedly until it returns false. At that time, close will be called.
#### Using the ResultSetHandle Interface
-This interface is similar to the `ResultSetProvider` interface in that it has a `close()` method that will be called at the end. But instead of having the evaluator call a method that builds one row at a time, this method has a method that returns a `ResultSet`. The query evaluator will iterate over this set and deliver the `ResultSet` contents, one tuple at a time, to the caller until a call to `next()` returns false or the evaluator decides that no more rows are needed.
+Classes implementing the `ResultSetHandle` interface must include a method to return a `ResultSet` named `getResultSet()`. The query evaluator will iterate over this set and deliver the `ResultSet` contents, one tuple at a time, to the caller until a call to `next()` returns false or the evaluator decides that no more rows are needed.
-Here is an example that executes a query using a statement that it obtained using the default connection. The SQL suitable for the deployment descriptor looks like this:
+Example: Using the `ResultSetHandle` Interface
-```sql
-=> CREATE FUNCTION javatest.listSupers()
- RETURNS SETOF pg_user
- AS 'org.postgresql.pljava.example.Users.listSupers'
- LANGUAGE java;
-=> CREATE FUNCTION javatest.listNonSupers()
- RETURNS SETOF pg_user
- AS 'org.postgresql.pljava.example.Users.listNonSupers'
- LANGUAGE java;
-```
+1. A `Users` class is defined in the Java example package `org.postgresql.example` (refer to [`example`](https://github.com/apache/incubator-hawq/blob/master/src/pl/pljava/src/java/examples/org/postgresql/example) for example source files):
-And in the Java package `org.postgresql.pljava.example` a class `Users` is added:
-
-```java
-public class Users implements ResultSetHandle
-{
- private final String m_filter;
- private Statement m_statement;
- public Users(String filter)
- {
- m_filter = filter;
- }
- public ResultSet getResultSet()
- throws SQLException
- {
- m_statement =
- DriverManager.getConnection("jdbc:default:connection").cr
-eateStatement();
- return m_statement.executeQuery("SELECT * FROM pg_user
- WHERE " + m_filter);
- }
-
- public void close()
- throws SQLException
- {
- m_statement.close();
- }
-
- public static ResultSetHandle listSupers()
- {
- return new Users("usesuper = true");
- }
-
- public static ResultSetHandle listNonSupers()
- {
- return new Users("usesuper = false");
- }
-}
-```
+ ``` java
+ package org.postgresql.example;
+
+ import java.sql.DriverManager;
+ import java.sql.ResultSet;
+ import java.sql.SQLException;
+ import java.sql.Statement;
+
+ import org.postgresql.pljava.ResultSetHandle;
+
+ public class Users implements ResultSetHandle
+ {
+ private final String m_filter;
+ private Statement m_statement;
+ public Users(String filter)
+ {
+ m_filter = filter;
+ }
+ public ResultSet getResultSet()
+ throws SQLException
+ {
+ m_statement =
+ DriverManager.getConnection("jdbc:default:connection").createStatement();
+ return m_statement.executeQuery("SELECT * FROM pg_user
+ WHERE " + m_filter);
+ }
+
+ public void close()
+ throws SQLException
+ {
+ m_statement.close();
+ }
+
+ public static ResultSetHandle listSupers()
+ {
+ return new Users("usesuper = true");
+ }
+
+ public static ResultSetHandle listNonSupers()
+ {
+ return new Users("usesuper = false");
+ }
+ }
+ ```
+
+ The `listSupers()` and `listNonSupers()` methods each execute a query to return a `ResultSetHandle` containing a list of those users with and without superuser privileges, respectively.
+
+2. Copy the `examples.jar` file to the default PL/Java classpath directory:
+
+ ``` shell
+ $ cp /usr/local/hawq/share/postgresql/pljava/examples.jar /usr/local/hawq/lib/postgresql/java/
+ ```
+
+2. Start the `psql` subsystem:
+
+ ``` shell
+ $ psql -d testdb
+ ```
+
+6. Add the `examples.jar` JAR file to the session-level classpath:
+
+ ``` sql
+ => SET pljava_classpath='examples.jar';
+ ```
+
+7. Create PL/Java UDFs that execute the `listSupers()` and `listNonSupers()` methods in the `org.postgresql.example.Users` class:
+
+ ``` sql
+ => CREATE FUNCTION plistSupers()
+ RETURNS SETOF pg_user
+ AS 'org.postgresql.example.Users.listSupers'
+ LANGUAGE java;
+ => CREATE FUNCTION plistNonSupers()
+ RETURNS SETOF pg_user
+ AS 'org.postgresql.example.Users.listNonSupers'
+ LANGUAGE java;
+ ```
+
+8. Execute the UDFs:
+
+ ``` sql
+ => SELECT plistSupers();
+ ```
+
+ ```
+ plistsupers
+ -------------------------------
+ (gpadmin,10,t,t,t,********,,)
+ (1 row)
+ ```
+
+ ``` sql
+ => SELECT plistNonSupers();
+ ```
+
+ ```
+ plistnonsupers
+ ---------------
+ (0 rows)
+ ```
+
+
## <a id="usingjdbc"></a>Using JDBC
-PL/Java contains a JDBC driver that maps to the PostgreSQL SPI functions. A connection that maps to the current transaction can be obtained using the following statement:
+PL/Java includes a JDBC driver. This driver invokes HAWQ internal SPI routines. The driver is essential; it is common for functions to make calls back to the database to fetch data. When PL/Java user-defined functions fetch data, they must use the same transactional boundaries that are used by the main function that entered the PL/Java execution context.
+
+You can obtain a PL/Java JDBC driver connection mapping to the current transaction with the following Java statement:
-```java
+``` java
Connection conn =
DriverManager.getConnection("jdbc:default:connection");
```
-After obtaining a connection, you can prepare and execute statements similar to other JDBC connections. These are limitations for the PL/Java JDBC driver:
+After obtaining a connection, you can prepare and execute statements similar to other JDBC connections. Refer to the `Users` class Java source code from the example above.
+
+Limitations of the PL/Java JDBC driver include the following:
-- The transaction cannot be managed in any way. Thus, you cannot use methods on the connection such as:
+- A transaction cannot be managed in any way. As such, you cannot use the following methods on the JDBC connection:
- `commit()`
- `rollback()`
- `setAutoCommit()`
- `setTransactionIsolation()`
-- Savepoints are available with some restrictions. A savepoint cannot outlive the function in which it was set and it must be rolled back or released by that same function.
-- A ResultSet returned from `executeQuery()` are always `FETCH_FORWARD` and `CONCUR_READ_ONLY`.
-- Meta-data is only available in PL/Java 1.1 or higher.
+- Savepoints are available, with some restrictions. A savepoint cannot outlive the function in which it was set, and it must be rolled back or released by that same function.
+- A `ResultSet` returned from `executeQuery()` is always `FETCH_FORWARD` and `CONCUR_READ_ONLY`.
- `CallableStatement` (for stored procedures) is not implemented.
-- The types `Clob` or `Blob` are not completely implemented, they need more work. The types `byte[]` and `String` can be used for `bytea` and `text` respectively.
+- The types `Clob` and `Blob` are not completely implemented. Use the types `byte[]` and `String` for `bytea` and `text`, respectively.
## <a id="exceptionhandling"></a>Exception Handling
-You can catch and handle an exception in the HAWQ backend just like any other exception. The backend `ErrorData` structure is exposed as a property in a class called `org.postgresql.pljava.ServerException` (derived from `java.sql.SQLException`) and the Java try/catch mechanism is synchronized with the backend mechanism.
+You can catch and handle an exception in the HAWQ backend just like any other exception. The backend `ErrorData` structure is exposed as a property in the `org.postgresql.pljava.internal.ServerException` class (derived from `java.sql.SQLException`), and the Java `try/catch` construct is synchronized with the backend mechanism.
-**Important:** You will not be able to continue executing backend functions until your function has returned and the error has been propagated when the backend has generated an exception unless you have used a savepoint. When a savepoint is rolled back, the exceptional condition is reset and you can continue your execution.
+**Important:** If the backend generates an exception and you have set a savepoint, the exception condition is reset when the savepoint is rolled back, allowing you to continue your execution. If the backend has generated an exception and you have *not* used a savepoint, then you cannot execute backend functions until your function propagates the error and returns.
## <a id="savepoints"></a>Savepoints
-HAWQ savepoints are exposed using the `java.sql.Connection` interface. Two restrictions apply.
+HAWQ exposes savepoints using the `java.sql.Connection` interface. Two restrictions apply:
-- A savepoint must be rolled back or released in the function where it was set.
-- A savepoint must not outlive the function where it was set.
+- A savepoint must be rolled back or released in the function in which it was set.
+- A savepoint must not outlive the function from which it was set.
## <a id="logging"></a>Logging
-PL/Java uses the standard Java Logger. Hence, you can write things like:
+PL/Java uses the standard Java Logger. For example:
-```java
+``` java
Logger.getAnonymousLogger().info( "Time is " + new
-Date(System.currentTimeMillis()));
+ Date(System.currentTimeMillis()));
```
-At present, the logger uses a handler that maps the current state of the HAWQ configuration setting `log_min_messages` to a valid Logger level and that outputs all messages using the HAWQ backend function `elog()`.
+The logger uses a handler that maps the current setting of the HAWQ `log_min_messages` server configuration parameter to a valid Java logging level, using the HAWQ backend function `elog()` to output all messages.
-**Note:** The `log_min_messages` setting is read from the database the first time a PL/Java function in a session is executed. On the Java side, the setting does not change after the first PL/Java function execution in a specific session until the HAWQ session that is working with PL/Java is restarted.
+**Note:** The `log_min_messages` setting is read from the database the first time a PL/Java function in a session is executed and can only be changed by restarting the HAWQ session.
-The following mapping apply between the Logger levels and the HAWQ backend levels.
+The table below identifies the Java to HAWQ log level mapping:
-***Table 2: PL/Java Logging Levels Mappings***
+**Table 2: PL/Java Logging Levels Mappings**
| java.util.logging.Level | HAWQ Level |
|-------------------------|------------|
-| SEVERE ERROR | ERROR |
+| SEVERE | ERROR |
| WARNING | WARNING |
| CONFIG | LOG |
| INFO | INFO |
@@ -606,104 +962,144 @@ The following mapping apply between the Logger levels and the HAWQ backend level
| FINER | DEBUG2 |
| FINEST | DEBUG3 |
-## <a id="security"></a>Security
-
-This section describes security aspects of using PL/Java.
-### <a id="installation"></a>Installation
-
-Only a database super user can install PL/Java. The PL/Java utility functions are installed using SECURITY DEFINER so that they execute with the access permissions that where granted to the creator of the functions.
-
-### <a id="trustedlang"></a>Trusted Language
+## <a id="pljavaexample"></a>Example
-PL/Java is a trusted language. The trusted PL/Java language has no access to the file system as stipulated by PostgreSQL definition of a trusted language. Any database user can create and access functions in a trusted language.
+The following example:
-PL/Java also installs a language handler for the language `javau`. This version is not trusted and only a superuser can create new functions that use it. Any user can call the functions.
+1. Creates a Java class with a single method.
+2. Creates a PL/Java UDF that invokes the method.
+3. Executes the PL/Java UDF on a simple table.
+**Note**: This example requires a Java SDK be installed on your development system.
-## <a id="pljavaexample"></a>Example
+1. Create a work area for the example:
-The following simple Java example creates a JAR file that contains a single method and runs the method.
+ ``` shell
+ $ mkdir -p $PLJAVAWORK/example/com/exco/app
+ $ cd $PLJAVAWORK/example/com/exco/app
+ ```
-<p class="note"><b>Note:</b> The example requires Java SDK to compile the Java file.</p>
+2. Create a new file named `SubStr.java`, adding the following text to create a class named `SubStr` with a single method named `substring()`. This method takes a text string and two index integers as input and returns the string bounded by the indexes:
-The following method returns a substring.
+ ``` shell
+ $ vi SubStr.java
+ ```
-```java
-{
-public static String substring(String text, int beginIndex,
- int endIndex)
+ ``` java
+ package com.exco.app;
+ import java.util.logging.Logger;
+
+ public class SubStr
{
- return text.substring(beginIndex, endIndex);
+ public static String substring(String text, int beginIndex, int endIndex)
+ {
+ return text.substring(beginIndex, endIndex);
+ }
}
-}
-```
-
-Enter the Java code in a text file `example.class`.
+ ```
-Contents of the file `manifest.txt`:
+3. Compile the `SubStr` class, create a JAR file named `exsubstr.jar`, and copy this JAR file to the default PL/Java classpath directory:
-```plaintext
-Manifest-Version: 1.0
-Main-Class: Example
-Specification-Title: "Example"
-Specification-Version: "1.0"
-Created-By: 1.6.0_35-b10-428-11M3811
-Build-Date: 01../2013 10:09 AM
-```
-
-Compile the Java code:
+ ``` shell
+ $ javac *.java
+ $ cd ../../..
+ $ jar cf exsubstr.jar com
+ $ cp exsubstr.jar /usr/local/hawq/lib/postgresql/java/
+ ```
+
+5. Start the `psql` subsystem:
-```shell
-$ javac *.java
-```
+ ``` shell
+ $ psql -d testdb
+ ```
-Create a JAR archive named `analytics.jar` that contains the class file and the manifest file in the JAR:
+6. Add the JAR file to the session-level classpath:
-```shell
-$ jar cfm analytics.jar manifest.txt *.class
-```
+ ``` sql
+ => SET pljava_classpath='exsubstr.jar';
+ ```
-Upload the JAR file to the HAWQ master host.
+7. Create a HAWQ internal table named `extblwstrings`:
-Run the `hawq scp` utility to copy the jar file to the HAWQ Java directory. Use the `-f` option to specify the file that contains a list of the master and segment hosts:
+ ``` sql
+ => CREATE TABLE extblwstrings (a varchar) DISTRIBUTED randomly;
+ ```
-```shell
-$ hawq scp -f hawq_hosts analytics.jar =:/usr/local/hawq/lib/postgresql/java/
-```
+8. Insert some string data into the `extblwstrings` table:
-Add the `analytics.jar` JAR file to the `pljava_classpath` configuration parameter. Refer to [Setting PL/Java Configuration Parameters](#setting_serverconfigparams) for the specific procedure.
+ ``` sql
+ => INSERT INTO extblwstrings VALUES ('my string');
+ => INSERT INTO extblwstrings VALUES ('secondstring');
+ => INSERT INTO extblwstrings VALUES ('abcdefghij');
+ ```
-From the `psql` subsystem, run the following command to show the installed JAR files:
+9. Create a PL/Java UDF that invokes the `com.exco.app.SubStr` class `substring()` method defined earlier:
-``` sql
-=> SHOW pljava_classpath
-```
+ ``` sql
+ => CREATE OR REPLACE FUNCTION ex_getsubstring(varchar, int, int)
+ RETURNS varchar AS 'com.exco.app.SubStr.substring'
+ LANGUAGE java;
+ ```
-The following SQL commands create a table and define a Java function to test the method in the JAR file:
+8. Execute the UDF:
-```sql
-=> CREATE TABLE temp (a varchar) DISTRIBUTED randomly;
-=> INSERT INTO temp values ('my string');
---Example function
-=> CREATE OR REPLACE FUNCTION java_substring(varchar, int, int)
- RETURNS varchar AS 'Example.substring'
- LANGUAGE java;
---Example execution
-=> SELECT java_substring(a, 1, 5) FROM temp;
-```
+ ``` sql
+ => SELECT ex_getsubstring( a, 3, 7) FROM extblwstrings;
+ ```
+
+ ```
+ ex_getsubstring
+ -----------------
+ stri
+ onds
+ defg
+ (3 rows)
+ ```
-If you add these SQL commands to a file named `mysample.sql`, you can run the commands from the `psql` subsystem using the `\i` meta-command:
+10. If you add each of the above SQL commands to a file, you can run the commands from the command line or the `psql` subsystem. Add the following text to a file named `/tmp/myexample.sql`:
-``` sql
-=> \i mysample.sql
-```
+ ``` shell
+ $ vi /tmp/myexample.sql
+ ```
+
+ ```
+ DROP TABLE IF EXISTS extblwstrings;
+ SET pljava_classpath='exsubstr.jar';
+ CREATE TABLE extblwstrings (a varchar) DISTRIBUTED randomly;
+ INSERT INTO extblwstrings VALUES ('my string');
+ INSERT INTO extblwstrings VALUES ('secondstring');
+ INSERT INTO extblwstrings VALUES ('abcdefghij');
+ CREATE OR REPLACE FUNCTION ex_getsubstring(varchar, int, int)
+ RETURNS varchar AS 'com.exco.app.SubStr.substring'
+ LANGUAGE java;
+ SELECT ex_getsubstring( a, 3, 7) FROM extblwstrings;
+ ```
-The output is similar to this:
+11. Run the `myexample.sql` script file:
-```shell
-java_substring
-----------------
- y st
-(1 row)
-```
\ No newline at end of file
+ ``` shell
+ $ psql -d testdb -f /tmp/myexample.sql
+ DROP TABLE
+ SET
+ CREATE TABLE
+ INSERT 0 1
+ INSERT 0 1
+ INSERT 0 1
+ CREATE FUNCTION
+ ex_getsubstring
+ -----------------
+ stri
+ onds
+ defg
+ (3 rows)
+ ```
+
+ ``` shell
+ $ psql -d testdb
+ ```
+
+ ``` sql
+ => \i /tmp/myexample.sql
+ ...
+ ```
[5/7] incubator-hawq-docs git commit: HAWQ-1272 Modify code sample to
use standard SQL syntax (closes #87)
Posted by yo...@apache.org.
HAWQ-1272 Modify code sample to use standard SQL syntax (closes #87)
Project: http://git-wip-us.apache.org/repos/asf/incubator-hawq-docs/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-hawq-docs/commit/87328a58
Tree: http://git-wip-us.apache.org/repos/asf/incubator-hawq-docs/tree/87328a58
Diff: http://git-wip-us.apache.org/repos/asf/incubator-hawq-docs/diff/87328a58
Branch: refs/heads/release/2.1.0.0-incubating
Commit: 87328a58b029aae030ab7542a5862d4d664ff6fb
Parents: de32485
Author: Jane Beckman <jb...@pivotal.io>
Authored: Mon Jan 16 15:15:14 2017 -0800
Committer: David Yozie <yo...@apache.org>
Committed: Mon Jan 16 15:15:14 2017 -0800
----------------------------------------------------------------------
markdown/reference/toolkit/hawq_toolkit.html.md.erb | 6 +++---
1 file changed, 3 insertions(+), 3 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-hawq-docs/blob/87328a58/markdown/reference/toolkit/hawq_toolkit.html.md.erb
----------------------------------------------------------------------
diff --git a/markdown/reference/toolkit/hawq_toolkit.html.md.erb b/markdown/reference/toolkit/hawq_toolkit.html.md.erb
index ac5db66..b898061 100644
--- a/markdown/reference/toolkit/hawq_toolkit.html.md.erb
+++ b/markdown/reference/toolkit/hawq_toolkit.html.md.erb
@@ -101,9 +101,9 @@ The `hawq_size_*` family of views can be used to determine the disk space usage
The table and index sizing views list the relation by object ID (not by name). To check the size of a table or index by name, you must look up the relation name (`relname`) in the `pg_class` table. For example:
``` pre
-SELECT relname as name, sotdsize as size, sotdtoastsize as
-toast, sotdadditionalsize as other
-FROM hawq_size_of_table_disk as sotd, pg_class
+SELECT relname AS name, sotdsize AS size, sotdtoastsize AS
+toast, sotdadditionalsize AS other
+FROM hawq_size_of_table_disk AS sotd, pg_class
WHERE sotd.sotdoid=pg_class.oid ORDER BY relname;
```
[4/7] incubator-hawq-docs git commit: empty commit to force mirror
synchronization
Posted by yo...@apache.org.
empty commit to force mirror synchronization
Project: http://git-wip-us.apache.org/repos/asf/incubator-hawq-docs/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-hawq-docs/commit/de32485c
Tree: http://git-wip-us.apache.org/repos/asf/incubator-hawq-docs/tree/de32485c
Diff: http://git-wip-us.apache.org/repos/asf/incubator-hawq-docs/diff/de32485c
Branch: refs/heads/release/2.1.0.0-incubating
Commit: de32485cbd50dcbb76ddf705684770a91a4a8a00
Parents: e04f7f8
Author: David Yozie <yo...@apache.org>
Authored: Mon Jan 16 08:30:22 2017 -0800
Committer: David Yozie <yo...@apache.org>
Committed: Mon Jan 16 08:30:22 2017 -0800
----------------------------------------------------------------------
----------------------------------------------------------------------
[3/7] incubator-hawq-docs git commit: HAWQ-1251 Add CREATE
CAST/CONVERSION pages to documentation (closes #85)
Posted by yo...@apache.org.
HAWQ-1251 Add CREATE CAST/CONVERSION pages to documentation (closes #85)
Project: http://git-wip-us.apache.org/repos/asf/incubator-hawq-docs/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-hawq-docs/commit/e04f7f89
Tree: http://git-wip-us.apache.org/repos/asf/incubator-hawq-docs/tree/e04f7f89
Diff: http://git-wip-us.apache.org/repos/asf/incubator-hawq-docs/diff/e04f7f89
Branch: refs/heads/release/2.1.0.0-incubating
Commit: e04f7f89ded867efb3ab4fc15e7aa2e8d063232e
Parents: 04bf221
Author: Jane Beckman <jb...@pivotal.io>
Authored: Fri Jan 13 15:46:53 2017 -0800
Committer: David Yozie <yo...@apache.org>
Committed: Fri Jan 13 15:46:53 2017 -0800
----------------------------------------------------------------------
.../source/subnavs/apache-hawq-nav-210.erb | 5 +
.../reference/sql/ALTER-CONVERSION.html.md.erb | 56 ++++++++++++
markdown/reference/sql/CREATE-CAST.html.md.erb | 96 ++++++++++++++++++++
.../reference/sql/CREATE-CONVERSION.html.md.erb | 67 ++++++++++++++
markdown/reference/sql/DROP-CAST.html.md.erb | 48 ++++++++++
.../reference/sql/DROP-CONVERSION.html.md.erb | 48 ++++++++++
6 files changed, 320 insertions(+)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-hawq-docs/blob/e04f7f89/book/master_middleman/source/subnavs/apache-hawq-nav-210.erb
----------------------------------------------------------------------
diff --git a/book/master_middleman/source/subnavs/apache-hawq-nav-210.erb b/book/master_middleman/source/subnavs/apache-hawq-nav-210.erb
index 1c7449f..5d0306f 100644
--- a/book/master_middleman/source/subnavs/apache-hawq-nav-210.erb
+++ b/book/master_middleman/source/subnavs/apache-hawq-nav-210.erb
@@ -692,6 +692,7 @@
<ul>
<li><a href="/docs/userguide/2.1.0.0-incubating/reference/sql/ABORT.html">ABORT</a></li>
<li><a href="/docs/userguide/2.1.0.0-incubating/reference/sql/ALTER-AGGREGATE.html">ALTER AGGREGATE</a></li>
+ <li><a href="/docs/userguide/2.1.0.0-incubating/reference/sql/ALTER-CONVERSION.html">ALTER CONVERSION</a></li>
<li><a href="/docs/userguide/2.1.0.0-incubating/reference/sql/ALTER-DATABASE.html">ALTER DATABASE</a></li>
<li><a href="/docs/userguide/2.1.0.0-incubating/reference/sql/ALTER-FUNCTION.html">ALTER FUNCTION</a></li>
<li><a href="/docs/userguide/2.1.0.0-incubating/reference/sql/ALTER-OPERATOR.html">ALTER OPERATOR</a></li>
@@ -709,6 +710,8 @@
<li><a href="/docs/userguide/2.1.0.0-incubating/reference/sql/COMMIT.html">COMMIT</a></li>
<li><a href="/docs/userguide/2.1.0.0-incubating/reference/sql/COPY.html">COPY</a></li>
<li><a href="/docs/userguide/2.1.0.0-incubating/reference/sql/CREATE-AGGREGATE.html">CREATE AGGREGATE</a></li>
+ <li><a href="/docs/userguide/2.1.0.0-incubating/reference/sql/CREATE-CAST.html">CREATE CAST</a></li>
+ <li><a href="/docs/userguide/2.1.0.0-incubating/reference/sql/CREATE-CONVERSION.html">CREATE CONVERSION</a></li>
<li><a href="/docs/userguide/2.1.0.0-incubating/reference/sql/CREATE-DATABASE.html">CREATE DATABASE</a></li>
<li><a href="/docs/userguide/2.1.0.0-incubating/reference/sql/CREATE-EXTERNAL-TABLE.html">CREATE EXTERNAL TABLE</a></li>
<li><a href="/docs/userguide/2.1.0.0-incubating/reference/sql/CREATE-FUNCTION.html">CREATE FUNCTION</a></li>
@@ -729,6 +732,8 @@
<li><a href="/docs/userguide/2.1.0.0-incubating/reference/sql/DEALLOCATE.html">DEALLOCATE</a></li>
<li><a href="/docs/userguide/2.1.0.0-incubating/reference/sql/DECLARE.html">DECLARE</a></li>
<li><a href="/docs/userguide/2.1.0.0-incubating/reference/sql/DROP-AGGREGATE.html">DROP AGGREGATE</a></li>
+ <li><a href="/docs/userguide/2.1.0.0-incubating/reference/sql/DROP-CAST.html">DROP CAST</a></li>
+ <li><a href="/docs/userguide/2.1.0.0-incubating/reference/sql/DROP-CONVERSION.html">DROP CONVERSION</a></li>
<li><a href="/docs/userguide/2.1.0.0-incubating/reference/sql/DROP-DATABASE.html">DROP DATABASE</a></li>
<li><a href="/docs/userguide/2.1.0.0-incubating/reference/sql/DROP-EXTERNAL-TABLE.html">DROP EXTERNAL TABLE</a></li>
<li><a href="/docs/userguide/2.1.0.0-incubating/reference/sql/DROP-FILESPACE.html">DROP FILESPACE</a></li>
http://git-wip-us.apache.org/repos/asf/incubator-hawq-docs/blob/e04f7f89/markdown/reference/sql/ALTER-CONVERSION.html.md.erb
----------------------------------------------------------------------
diff --git a/markdown/reference/sql/ALTER-CONVERSION.html.md.erb b/markdown/reference/sql/ALTER-CONVERSION.html.md.erb
new file mode 100644
index 0000000..5b02ffc
--- /dev/null
+++ b/markdown/reference/sql/ALTER-CONVERSION.html.md.erb
@@ -0,0 +1,56 @@
+---
+title: ALTER CONVERSION
+---
+
+Changes the definition of a CONVERSION.
+
+## <a id="topic1__section2"></a>Synopsis
+
+``` pre
+ALTER CONVERSION <name> RENAME TO <newname>
+
+ALTER CONVERSION <name> OWNER TO <newowner>
+```
+
+## <a id="topic1__section3"></a>Description
+
+`ALTER CONVERSION` changes the definition of a conversion.
+
+You must own the conversion to use `ALTER CONVERSION`. To alter the owner, you must also be a direct or indirect member of the new owning role, and that role must have `CREATE` privilege on the conversion's schema. (These restrictions enforce that altering the owner does not do anything you could not do by dropping and recreating the conversion. However, a superuser can always alter ownership of any conversion.).
+
+## <a id="topic1__section4"></a>Parameters
+
+<dt>\<name\> </dt>
+<dd>The (optionally schema-qualified) name of an existing conversion.</dd>
+
+<dt>\<newname\> </dt>
+<dd>The new name of the conversion.</dd>
+
+<dt>\<newowner\> </dt>
+<dd>The new owner of the conversion.</dd>
+
+
+## <a id="topic1__section5"></a>Examples
+
+To rename the conversion `iso_8859_1_to_utf8` to `latin1_to_unicode`:
+
+``` pre
+ALTER CONVERSION iso_8859_1_to_utf8 RENAME TO
+latin1_to_unicode;
+```
+
+To change the owner of the conversion `iso_8859_1_to_utf8` to `joe`:
+
+``` pre
+ALTER CONVERSION iso_8859_1_to_utf8 OWNER TO joe;
+```
+
+## <a id="topic1__section6"></a>Compatibility
+
+There is no ALTER CONVERSION statement in the SQL standard.
+
+## <a id="topic1__section7"></a>See Also
+
+[CREATE CONVERSION](CREATE-CONVERSION.html), [DROP CONVERSION](DROP-CONVERSION.html)
+
+
http://git-wip-us.apache.org/repos/asf/incubator-hawq-docs/blob/e04f7f89/markdown/reference/sql/CREATE-CAST.html.md.erb
----------------------------------------------------------------------
diff --git a/markdown/reference/sql/CREATE-CAST.html.md.erb b/markdown/reference/sql/CREATE-CAST.html.md.erb
new file mode 100644
index 0000000..af35173
--- /dev/null
+++ b/markdown/reference/sql/CREATE-CAST.html.md.erb
@@ -0,0 +1,96 @@
+---
+title: CREATE CAST
+---
+
+Defines a new cast.
+
+## <a id="topic1__section2"></a>Synopsis
+
+``` pre
+CREATE CAST (<sourcetype> AS <targettype>)
+ WITH FUNCTION <funcname (argtypes)>
+ [AS ASSIGNMENT | AS IMPLICIT]
+
+CREATE CAST (<sourcetype> AS <targettype>) WITHOUT FUNCTION
+ [AS ASSIGNMENT | AS IMPLICIT]
+```
+
+## <a id="topic1__section3"></a>Description
+
+`CREATE CAST` defines a new cast. A cast specifies how to perform a conversion between two data types. For example,
+
+```pre
+SELECT CAST(42 AS text);
+```
+
+converts the integer constant `42` to type `text` by invoking a previously specified function, in this case `text(int4)`. If no suitable cast has been defined, the conversion fails.
+
+Two types may be binary compatible, which means that they can be converted into one another without invoking any function. This requires that corresponding values use the same internal representation. For instance, the types `text` and `varchar` are binary compatible.
+
+By default, a cast can be invoked only by an explicit cast request, that is an explicit `CAST(x AS typename)` or `x:: typename` construct.
+
+If the cast is marked `AS ASSIGNMENT` then it can be invoked implicitly when assigning a value to a column of the target data type. For example, supposing that `foo.f1` is a column of type `text`, then:
+
+``` pre
+INSERT INTO foo (f1) VALUES (42);
+```
+will be allowed if the cast from type integer to type `text` is marked `AS ASSIGNMENT`, otherwise not. The term *assignment cast* is typically used to describe this kind of cast.
+
+If the cast is marked `AS IMPLICIT` then it can be invoked implicitly in any context, whether by assignment or internally in an expression. The term *implicit cast* is typically used to describe this kind of cast. For example, since `||` takes `text` operands,
+
+``` pre
+SELECT 'The time is ' || now();
+```
+It is wise to be conservative about marking casts `AS IMPLICIT`. An overabundance of implicit casting paths can cause HAWQ to choose surprising interpretations of commands, or to be unable to resolve commands at all because there are multiple possible interpretations. A good rule of thumb is to make a cast implicitly invokable only for information-preserving transformations between types in the same general type category. For example, the cast from `int2` to `int4` can reasonably be implicit, but the cast from `float8` to `int4` should probably be assignment-only. Cross-type-category casts, such as `text` to `int4`, are best made explicit-only.
+
+To be able to create a cast, you must own the source or the target data type. To create a binary-compatible cast, you must be superuser.
+
+
+## <a id="topic1__section4"></a>Parameters
+
+<dt>\<sourcetype\> </dt>
+<dd>The name of the source data type of the cast.</dd>
+
+<dt>\<targettype\> </dt>
+<dd>The name of the target data type of the cast.</dd>
+
+<dt> \<funcname(argtypes)\> </dt>
+<dd>The function used to perform the cast. The function name may be schema-qualified. If it is not, the function is looked up in the schema search path. The function's result data type must match the target type of the cast.
+Cast implementation functions may have one to three arguments. The first argument type must be identical to the cast's source type. The second argument, if present, must be type `integer`; it receives the type modifier associated with the destination type, or `-1` if there is none. The third argument, if present, must be type `boolean`; it receives `true` if the cast is an explicit cast, `false` otherwise. In some cases, the SQL specification demands different behaviors for explicit and implicit casts. This argument is supplied for functions that must implement such casts. Designing your own data types this way is not recommended.
+Ordinarily, a cast must have different source and target data types. However, declaring a cast with identical source and target types is allowed if it has a cast implementation function with more than one argument. This is used to represent type-specific length coercion functions in the system catalogs. The named function is used to coerce a value of the type to the type modifier value specified by its second argument. (Since the grammar presently permits only certain built-in data types to have type modifiers, this feature is of no use for user-defined target types.)
+When a cast has different source and target types and a function that takes more than one argument, it represents a conversion from one type to another and applying a length coercion in a single step. When no such entry is available, coercion to a type that uses a type modifier involves two steps, one to convert between data types and a second to apply the modifier.
+</dd>
+
+<dt>WITHOUT FUNCTION</dt>
+<dd>Indicates that the source type and the target type are binary compatible, so no function is required to perform the cast.</dd>
+
+<dt>AS ASSIGNMENT </dt>
+<dd>Indicates that the cast may be invoked implicitly in assignment contexts.</dd>
+
+<dt>AS IMPLICIT </dt>
+<dd>Indicates that the cast may be invoked implicitly in any context.</dd>
+
+## <a id="topic1__section5"></a>Notes
+
+For this release of HAWQ, user-defined functions used in a user-defined cast must be defined as `IMMUTABLE`. Any compiled code (shared library files) for custom functions must be placed in the same location on every host in your HAWQ array (master and all segments). This location must also be in the `LD_LIBRARY_PATH` so that the server can locate the files.
+
+Remember that if you want to be able to convert types both ways, you need to declare casts both ways explicitly.
+
+We recommend that you follow the convention of naming cast implementation functions after the target data type, as the built-in cast implementation functions are named. Many users are used to being able to cast data types using a function-style notation, that is `typename(x)`.
+
+## <a id="topic1__section6"></a>Examples
+
+Create a cast from type `text` to type `int4` using the function `int4(text)`. (This cast is already predefined in the system.):
+
+``` pre
+CREATE CAST (text AS int4) WITH FUNCTION int4(text);
+```
+
+## <a id="topic1__section7"></a>Compatibility
+
+The `CREATE CAST` command conforms to the SQL standard, except that SQL does not make provisions for binary-compatible types or extra arguments to implementation functions. `AS IMPLICIT` is also a HAWQ extension.
+
+## <a id="topic1__section8"></a>See Also
+
+[DROP CAST](DROP-CAST.html), [CREATE FUNCTION](CREATE-FUNCTION.html)
+
http://git-wip-us.apache.org/repos/asf/incubator-hawq-docs/blob/e04f7f89/markdown/reference/sql/CREATE-CONVERSION.html.md.erb
----------------------------------------------------------------------
diff --git a/markdown/reference/sql/CREATE-CONVERSION.html.md.erb b/markdown/reference/sql/CREATE-CONVERSION.html.md.erb
new file mode 100644
index 0000000..5bc7910
--- /dev/null
+++ b/markdown/reference/sql/CREATE-CONVERSION.html.md.erb
@@ -0,0 +1,67 @@
+---
+title: CREATE CONVERSION
+---
+
+Defines a new encoding conversion.
+
+## <a id="topic1__section2"></a>Synopsis
+
+``` pre
+CREATE [DEFAULT] CONVERSION <name> FOR <source_encoding> TO
+ <dest_encoding> FROM <funcname>
+```
+
+## <a id="topic1__section3"></a>Description
+
+`CREATE CONVERSION` defines a new conversion between character set encodings. Conversion names may be used in the convert function to specify a particular encoding conversion. Also, conversions that are marked `DEFAULT` can be used for automatic encoding conversion between client and server. For this purpose, two conversions, from encoding A to B and from encoding B to A, must be defined.
+
+To create a conversion, you must have `EXECUTE` privilege on the function and `CREATE` privilege on the destination schema.
+
+
+## <a id="topic1__section4"></a>Parameters
+
+<dt>DEFAULT</dt>
+<dd>Indicates that this conversion is the default for this particular source to destination encoding. There should be only one default encoding in a schema for the encoding pair.</dd>
+
+<dt>\<name\> </dt>
+<dd>The name of the conversion. The conversion name may be schema-qualified. If it is not, the conversion is defined in the current schema. The conversion name must be unique within a schema.</dd>
+
+<dt>\<source_encoding\> </dt>
+<dd>The source encoding name.</dd>
+
+<dt>\<dest_encoding\> </dt>
+<dd>The destination encoding name.</dd>
+
+<dt>\<funcname\> </dt>
+<dd>The function used to perform the conversion. The function name may be schema-qualified. If it is not, the function will be looked up in the path. The function must have the following signature:</dd>
+
+``` pre
+conv_proc(
+ integer, -- source encoding ID
+ integer, -- destination encoding ID
+ cstring, -- source string (null terminated C string)
+ internal, -- destination (fill with a null terminated C string)
+ integer -- source string length
+) RETURNS void;
+```
+
+## <a id="topic1__section5"></a>Notes
+
+For this release of HAWQ, user-defined functions used in a user-defined cast must be defined as `IMMUTABLE`. Any compiled code (shared library files) for custom functions must be placed in the same location on every host in your HAWQ array (master and all segments). This location must also be in the `LD_LIBRARY_PATH` so that the server can locate the files.
+
+
+## <a id="topic1__section6"></a>Examples
+
+To create a conversion from encoding `UTF8` to `LATIN1` using `myfunc`:
+
+``` pre
+CREATE CONVERSION myconv FOR 'UTF8' TO 'LATIN1' FROM myfunc;
+```
+
+## <a id="topic1__section7"></a>Compatibility
+
+There is no CREATE CONVERSION statement in the SQL standard.
+
+## <a id="topic1__section8"></a>See Also
+
+[DROP CONVERSION](DROP-CONVERSION.html), [CREATE FUNCTION](CREATE-FUNCTION.html)
http://git-wip-us.apache.org/repos/asf/incubator-hawq-docs/blob/e04f7f89/markdown/reference/sql/DROP-CAST.html.md.erb
----------------------------------------------------------------------
diff --git a/markdown/reference/sql/DROP-CAST.html.md.erb b/markdown/reference/sql/DROP-CAST.html.md.erb
new file mode 100644
index 0000000..dd8e928
--- /dev/null
+++ b/markdown/reference/sql/DROP-CAST.html.md.erb
@@ -0,0 +1,48 @@
+---
+title: DROP CAST
+---
+
+Removes a cast.
+
+## <a id="topic1__section2"></a>Synopsis
+
+``` pre
+DROP CAST [IF EXISTS] (<sourcetype> AS <targettype>) [CASCADE | RESTRICT]
+```
+
+## <a id="topic1__section3"></a>Description
+
+`DROP CAST` will delete a previously defined cast. To be able to drop a cast, you must own the source or the target data type. These are the same privileges that are required to create a cast.
+
+## <a id="topic1__section4"></a>Parameters
+
+<dt>IF EXISTS </dt>
+<dd>Do not throw an error if the cast does not exist. A notice is issued in this case.</dd>
+
+<dt>\<sourcetype\> </dt>
+<dd>The name of the source data type of the cast to be removed.</dd>
+
+<dt>\<targettype\> </dt>
+<dd>The name of the target data type of the cast to be removed.</dd>
+
+<dt>CASCADE</dt>
+<dt>RESTRICT</dt>
+<dd>These keywords have no effect since there are no dependencies on casts.</dd>
+
+## <a id="topic1__section5"></a>Examples
+
+To drop the cast from type `text` to type `int`:
+
+``` pre
+DROP CAST (text AS int);
+```
+
+## <a id="topic1__section6"></a>Compatibility
+
+The `DROP CAST` command conforms to the SQL standard.
+
+## <a id="topic1__section7"></a>See Also
+
+[CREATE CAST](CREATE-CAST.html)
+
+
http://git-wip-us.apache.org/repos/asf/incubator-hawq-docs/blob/e04f7f89/markdown/reference/sql/DROP-CONVERSION.html.md.erb
----------------------------------------------------------------------
diff --git a/markdown/reference/sql/DROP-CONVERSION.html.md.erb b/markdown/reference/sql/DROP-CONVERSION.html.md.erb
new file mode 100644
index 0000000..d435417
--- /dev/null
+++ b/markdown/reference/sql/DROP-CONVERSION.html.md.erb
@@ -0,0 +1,48 @@
+---
+title: DROP CONVERSION
+---
+
+Removes a CONVERSION.
+
+## <a id="topic1__section2"></a>Synopsis
+
+``` pre
+DROP CONVERSION [IF EXISTS] <name> [CASCADE | RESTRICT]
+```
+
+## <a id="topic1__section3"></a>Description
+
+`DROP CONVERSION` removes a previously defined conversion. To be able to drop a conversion, you must own the conversion.
+
+## <a id="topic1__section4"></a>Parameters
+
+<dt>IF EXISTS </dt>
+<dd>Do not throw an error if the conversion does not exist. A notice is issued in this case.</dd>
+
+<dt>\<name\> </dt>
+<dd>The name of the conversion. The conversion name may be schema-qualified.</dd>
+
+<dt>\<targettype\> </dt>
+<dd>The name of the target data type of the cast to be removed.</dd>
+
+<dt>CASCADE</dt>
+<dt>RESTRICT</dt>
+<dd>These keywords have no effect since there are no dependencies on conversions.</dd>
+
+## <a id="topic1__section5"></a>Examples
+
+Drop the conversion named `myname`:
+
+``` pre
+DROP CONVERSION myname;
+```
+
+## <a id="topic1__section6"></a>Compatibility
+
+There is no `DROP CONVERSION` statement in the SQL standard.
+
+## <a id="topic1__section7"></a>See Also
+
+[CREATE CONVERSION](CREATE-CONVERSION.html), [ALTER CONVERSION](ALTER-CONVERSION.html)
+
+