Skip to main content

Getting data into your H2O-3 Secure cluster

The first step toward building and scoring your models is getting your data into the H2O-3 Secure cluster/Java process that’s running on your local or remote machine. Whether you're importing data, uploading data, or retrieving data from HDFS or S3, be sure that your data is compatible with H2O-3 Secure.

Supported file formats​

H2O-3 Secure supports the following file types:

  • CSV (delimited, UTF-8 only) files (including GZipped CSV)
  • ORC
  • SVMLight
  • ARFF
  • XLS (BIFF 8 only)
  • XLSX (BIFF 8 only)
  • Avro version 1.8.0 (without multifile parsing or column type modification)
  • Parquet
  • Google Storage (gs://)
note

  • H2O supports UTF-8 encodings for CSV files. Please convert UTF-16 encodings to UTF-8 encoding before parsing CSV files into H2O-3 Secure.

  • Users can also import Hive files that are saved in ORC format (experimental).

  • When doing a parallel data import into a cluster:

    • If the data is an unzipped CSV file, H2O-3 Secure can do offset reads, so each node in your cluster can be directly reading its part of the CSV file in parallel.
    • If the data is zipped, H2O-3 Secure will have to read the whole file and unzip it before doing the parallel read.

If you have large data files on HDFS, use unzipped CSV. If the data is further away than the LAN, use zipped CSV.

warning
  • If you encounter issues importing XLS or XLSX files, you may be using an unsupported version. In this case, re-save the file in BIFF 8 format. Also note that XLS and XLSX support will eventually be deprecated.

Data sources​

H2O-3 Secure supports data ingest from various data sources. Natively, a local file system, remote file systems, HDFS, S3, and some relational databases are supported. Additional data sources can be accessed through a generic HDFS API, such as Alluxio or OpenStack Swift.

Default data sources​

  • Local File System
  • Remote File
  • S3
  • HDFS
  • JDBC
  • Hive

Local file system​

Data from a local machine can be uploaded to H2O-3 Secure through a push from the client. See more information on uploading from your local file system.

Remote file​

Data that is hosted on the Internet can be imported into H2O-3 Secure by specifying the URL. See more information on importing data from the internet.

HDFS-like data sources​

Various data sources can be accessed through an HDFS API. In this case, a library providing access to a data source needs to be passed on a command line when H2O-3 Secure is launched. The library must be compatible with the HDFS API in order to be registered as a correct HDFS FileSystem.

tip

Each node in the cluster must be launched in the same way.

Example HDFS-like data sources​

Required library

To access the Alluxio data source, an Alluxio client library that is part of Alluxio distribution is required. For example, alluxio-1.3.0/core/client/target/alluxio-core-client-1.3.0-jar-with-dependencies.jar.

H2O-3 Secure command line

java -cp alluxio-core-client-1.3.0-jar-with-dependencies.jar:build/h2o.jar water.H2OApp

URI scheme

An Alluxio data source is referenced using the alluxio:// schema and the location of the Alluxio master. For example,

alluxio://localhost:19998/iris.csv

core-site.xml configuration

Not supported.

JDBC databases​

Relational databases that include a JDBC (Java database connectivity) driver can be used as the source of data for machine learning in H2O-3 Secure. The supported SQL databases are MySQL, PostgreSQL, MariaDB, Netezza, Amazon Redshift, Teradata, and Hive. (See Hive JDBC driver for more information.) Data from these SQL databases can be pulled into H2O-3 Secure using the import_sql_table and import_sql_select functions.

See the following articles for examples about using JDBC data sources with H2O-3 Secure.

note

The handling of categorical values is different between file ingest and JDBC ingests. The JDBC treats categorical values as strings. Strings are not compressed in any way in H2O-3 Secure memory, and using the JDBC interface might need more memory and additional data post-processing (converting to categoricals explicitly).

import_sql_table function​

This function imports a SQL table to H2OFrame in memory. This function assumes that the SQL table is not being updated and is stable. You can run multiple SELECT SQL queries concurrently for parallel ingestion.

tip

Be sure to start the h2o.jar in the terminal with your downloaded JDBC driver in the classpath:

java -cp <path_to_h2o_jar>:<path_to_jdbc_driver_jar> water.H2OApp

The import_sql_table function accepts the following parameters:

  • connection_url: The URL of the SQL database connection as specified by the Java Database Connectivity (JDBC) Driver. For example, jdbc:mysql://localhost:3306/menagerie?&useSSL=false.
  • table: The name of the SQL table.
  • columns: A list of column names to import from SQL table. Defaults to importing all columns.
  • username: The username for the SQL server.
  • password: The password for the SQL server.
  • optimize: Specifies to optimize the import of the SQL table for faster imports. Note that this option is experimental.
  • fetch_mode: Set to DISTRIBUTED to enable distributed import. Set to SINGLE to force a sequential read by a single node from the database.
  • num_chunks_hint: Optionally specify the number of chunks for the target frame.
connection_url = "jdbc:mysql://172.16.2.178:3306/ingestSQL?&useSSL=false"
table = "citibike20k"
username = "root"
password = "abc123"
my_citibike_data = h2o.import_sql_table(connection_url, table, username, password)

import_sql_select function​

This function imports the SQL table that is the result of the specified SQL query to the H2OFrame in memory. It creates a temporary SQL table from the specified sql_query. You can run multiple SELECT SQL queries on the temporary table concurrently for parallel ingestion then drop the table.

tip

Be sure to start the h2o.jar in the terminal with your downloaded JDBC driver in the classpath:

java -cp <path_to_h2o_jar>:<path_to_jdbc_driver_jar> water.H2OApp

The import_sql_select function accepts the following parameters:

  • connection_url: URL of the SQL database connection as specified by the Java Database Connectivity (JDBC) Driver. For example, jdbc:mysql://localhost:3306/menagerie?&useSSL=false.
  • select_query: SQL query starting with SELECT that returns rows from one or more database tables.
  • username: The username for the SQL server.
  • password: The password for the SQL server.
  • optimize: Specifies to optimize import of the SQL table for faster imports. Note that this option is experimental.
  • use_temp_table: Specifies whether a temporary table should be created by select_query.
  • temp_table_name: The name of the temporary table to be created by select_query.
  • fetch_mode: Set to DISTRIBUTED to enable distributed import. Set to SINGLE to force a sequential read by a single node from the database.
connection_url = "jdbc:mysql://172.16.2.178:3306/ingestSQL?&useSSL=false"
select_query = "SELECT bikeid from citibike20k"
username = "root"
password = "abc123"
my_citibike_data = h2o.import_sql_select(connection_url, select_query, username, password)

Hive JDBC driver​

H2O-3 Secure can ingest data from Hive through the Hive JDBC driver (v2) by providing H2O-3 Secure with the JDBC driver for your Hive version. Explore this demo showing how to ingest data from Hive through the Hive v2 JDBC driver. The basic steps are described below.

tip

H2O-3 Secure can only load data from Hive version 2.2.0 or greater due to a limited implementation of the JDBC interface by Hive in earlier versions.

  1. Set up a table with data.

  1. Download this AirlinesTest dataset from S3.

  2. Run the CLI client for Hive:

beeline -u jdbc:hive2://hive-host:10000/db-name

  1. Create the DB table:

CREATE EXTERNAL TABLE IF NOT EXISTS AirlinesTest(
  fYear STRING ,
  fMonth STRING ,
  fDayofMonth STRING ,
  fDayOfWeek STRING ,
  DepTime INT ,
  ArrTime INT ,
  UniqueCarrier STRING ,
  Origin STRING ,
  Dest STRING ,
  Distance INT ,
  IsDepDelayed STRING ,
  IsDepDelayed_REC INT
)
    COMMENT 'test table'
    ROW FORMAT DELIMITED
    FIELDS TERMINATED BY ','
    LOCATION '/tmp';

  1. Import the data from the dataset (note that the file must be present on HDFS in /tmp):

LOAD DATA INPATH '/tmp/AirlinesTest.csv' OVERWRITE INTO TABLE AirlinesTest
  1. Retrieve the Hive JDBC client JAR.

Retrieve the driver from Maven for your desired version using mvn dependency:get -Dartifact=groupId:artifactId:version.

  1. Add the Hive JDBC driver to H2O-3 Secure's classpath:
# Add the Hive JDBC driver to H2O-3 Secure's classpath:
java -cp hive-jdbc.jar:<path_to_h2o_jar> water.H2OApp
  1. Initialize H2O-3 Secure in either Python or R and import your data:
# initialize h2o in Python
import h2o
h2o.init(extra_classpath = ["hive-jdbc-standalone.jar"])
  1. After the JAR file with the JDBC driver is added, the data from the Hive databases can be pulled into H2O-3 Secure using the aforementioned import_sql_table and import_sql_select functions.
connection_url = "jdbc:hive2://localhost:10000/default"
select_query = "SELECT * FROM AirlinesTest;"
username = "username"
password = "changeit"

airlines_dataset = h2o.import_sql_select(connection_url, 
                                         select_query, 
                                         username, 
                                         password)
Feedback