Impala-shell may have control sequence in its output

Assume you have a table has three rows, what is the result in the output file /tmp/my_table_count? 3? Actually it is not. There is a control sequence "ESC[?1034h" on my terminal.

$ impala-shell -B -q "select count(1) from my_table" > /tmp/my_table_count
$ xxd /tmp/my_table_count
0000000: 1b5b 3f31 3033 3468 320a                 .[?1034h2.

It will cause a problem when you use the result in a script which tries to update a partition's numRows in Impala.

  local a=$(impala-shell -B -q "select count(1) from my_table where part_col='2014-08-28'")
  impala-shell -q "alter table my_table partition(part_col='2014-08-20') set tblproperties('numRows'='$a')"

If you run this script, you will get the wrong value for #Rows due to the escape control sequence.

Query: show table stats my_table
+------------+-------+--------+--------+--------------+---------+
| part_col   | #Rows | #Files | Size   | Bytes Cached | Format  |
+------------+-------+--------+--------+--------------+---------+
| 2014-08-28 | -1    | 2      | 2.87KB | NOT CACHED   | PARQUET |
| Total      | -1    | 2      | 2.87KB | 0B           |         |
+------------+-------+--------+--------+--------------+---------+
Returned 2 row(s) in 0.06s

You can fix it by unsetting TERM like this:

  local a=$(TERM= impala-shell -B -q "select count(1) from my_table where part_col='2014-08-28'")
  impala-shell -q "alter table my_table partition(part_col='2014-08-20') set tblproperties('numRows'='$a')"

Set replication for files in Hadoop

• Change the existing files's replications

  hadoop fs -setrep -R -w 2 /data-dir
  

• Set replication when loading a file

  hadoop fs -Ddfs.replication=2 -put local_file dfs_file
  

Parquet Schema Incompatible between Pig and Hive

When you use Pig to process data and put into a Hive table, you need to be careful about the Pig namespace. It is possible that your complex Pig scripts may have namespace after group-by. And parquet.pig.ParquetStorer will keep the namespace prefix into the schema. Unfortunately, Hive Parquet map the table column to Parquet column by simply comparing string. See DataWritableReadSupport.java

  @Override
  public parquet.hadoop.api.ReadSupport.ReadContext init(final Configuration configuration,
      final Map keyValueMetaData, final MessageType fileSchema) {
    final String columns = configuration.get(IOConstants.COLUMNS);
    final Map contextMetadata = new HashMap();
    if (columns != null) {
      final List listColumns = getColumns(columns);

      final List typeListTable = new ArrayList();
      for (final String col : listColumns) {
        // listColumns contains partition columns which are metadata only
        if (fileSchema.containsField(col)) { // containsField return false because col doesn't have namespace.
          typeListTable.add(fileSchema.getType(col)); 
        } else {
          // below allows schema evolution
          typeListTable.add(new PrimitiveType(Repetition.OPTIONAL, PrimitiveTypeName.BINARY, col));
        }
      }

and GroupType.java:

  public boolean containsField(String name) {
    return indexByName.containsKey(name);
  }

There is not any name resolution. If you want Pig generate Hive readable Parquet files, you'd better give a lower-case name for each column before storing. I haven't tried whether writing into HCatalog table works or not. But HCatalog cannot write Parquet table in CDH 5.1.0, see my blog how to fix it.

Links of Hadoop Cluster Hardware

• HardWare
  • Cloudera: How-to: Select the Right Hardware for Your New Hadoop Cluster
  • Hortonworks: Best Practices for Selecting Apache Hadoop Hardware
  • Hortonworks: Chapter 1. Hardware Recommendations For Apache Hadoop
  • Pivotal: Pivotal HD Installation Overview: BEST PRACTICES FOR SELECTING HARDWARE
• Virtual Hadoop
• Disk setup
  • Apache Hadoop wiki: Setting up Disks for Hadoop
  • Hortonworks: Chapter 2. File System Partitioning Recommendations
• HP Solutions for Apache Hadoop
  • HP Reference Architecture for Cloudera Enterprise 5 on ProLiant DL Servers
• IBM System x Reference Architecture for Hadoop: IBM InfoSphere BigInsights Reference Architecture
• Dell | Cloudera Solution Reference Architecture v2.1.0

Teradata create a table like

create table target_db.target_table as src_db.src_table with no data; create table target_db.target_table as (select * from src_db.src_view) with no data;

HCatalog and Parquet

I want to use Sqoop to import Teradata tables into Impala's Parquet tables. Because Sqoop doesn't support to write Parquet files directly, it SEEMS very promising to use Sqoop HCatalog to write Parquet tables. Unfortunately I realized that it would not work at this time (CDH 5.1.0 with Hive 0.12) after several day's error and trial.

"Should never be used", you will see this error like this page. Hive 0.13 won't help too. Check out this Jira. This is a HCatalog problem.

I tried this solution:

• Dump data to an ORCFile table using sqoop hcatalog.
• Run a hive query to insert into the Parquet table.

So I need 30 mins to dump a big table to my small cluster and another 7 mins for Hive insert query.Because Impala doesn't support ORCFile, I have to convert the data into Parquet.

I hate this solution!!! This is why I studied the code of HCatalog to see why Parquet table failed. I figure outed a hack so that I can use HCatalog to dump data into Parquet directly.

The basic idea is to extend MapredParquetOutputFormat to support getRecordWriter. Checkout my github project hcat-parquet. The readme explains why HCatalog doesn't work with Parquet, and how to use the new FileOututFormat.

Run Spark Shell locally

If you want to run spark to access the local file system, here is the simple way:

HADOOP_CONF_DIR=. MASTER=local spark-shell

If you don't give HADOOP_CONF_DIR, spark will use /etc/hadoop/conf which may point to a cluster running in pseduo mode. When HADOOP_CONF_DIR points to a dir without any Hadoop configuration, the file system will be local. It also works for spark-submit.