Programming hbase oreilly pdf download

To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode characters Show hidden characters. This comment has been minimized. Sign in to view.

Copy link Quote reply. Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment. Show and hide more. Table of contents Product information. HBase Configuration Properties B. Road Map HBase 0. ISBN: Get it now. The metastore stores metadata such as table schema and parti- tion information that you specify when you run commands such as create table x Because multiple users and systems are likely to need concurrent access to the metastore, the default embedded database is not suitable for production.

If you are using a single node in pseudodistributed mode, you may not find it useful to set up a full relational database for the metastore. Any JDBC-compliant database can be used for the metastore. It is straightforward to adapt this information to other JDBC-compliant databases. The information required for table schema, partition information, etc.

However because it represents a Single Point of Failure SPOF , it is strongly recommended that you replicate and back up this database using the standard techniques you would nor- mally use with other relational database instances.

For our MySQL configuration, we need to know the host and port the service is running on. We will assume db1. We define these properties in Example Metastore database configuration in hive-site. Some teams put all such support libraries in their Hadoop lib directory.

With the driver and the configuration settings in place, Hive will store its metastore information in MySQL. Here is the output for Hive v0. There are several services available, including the CLI that we will spend most of our time using. You can invoke a service using the --service name option, although there are shorthand invocations for some of the services, as well.

Table describes the most useful services. Hive services Option Name Description cli Command-line interface Used to define tables, run queries, etc. It is the default service if no other service is specified. See Chapter 16 for more details. Using the CLI, you can create tables, inspect schema and query tables, etc. Here we show the output for Hive v0. For Hive v0. X, the -d, --hivevar, and -p options are not supported.

Both let you define on the command line custom variables that you can refer- ence in Hive scripts to customize execution. This feature is only supported in Hive v0. The terms variable or property are used in different contexts, but they function the same way in most cases.

The namespace options are described in Table The Command-Line Interface 31 www. For ex- ample, the following session shows the value for one variable, in the env namespace, and then all variable definitions!

Without the -v flag, set prints all the variables in the namespaces hivevar, hiveconf, system, and env.

The set command is also used to set new values for variables. The --hivevar flag is the same as the --define flag. Variable references in queries are replaced in the CLI before the query is sent to the query processor. Consider the following hive CLI session v0. It is used for all properties that configure Hive behavior.

It turns on printing of the current working database name in the CLI prompt. The default database is named default. We can even add new hiveconf entries, which is the only supported option for Hive versions earlier than v0. The Command-Line Interface 33 www. Unlike hivevar variables, you have to use the system: or env: prefix with system prop- erties and environment variables.

The env namespace is useful as an alternative way to pass variable definitions to Hive, especially for Hive v0. If you are using Hive v0. X, some of the examples in this book that use parameters and variables may not work as written. If so, replace the variable reference with the corresponding value.

It also shows the default values for each property. The CLI accepts a -e command argument that enables this feature. Adding the -S for silent mode removes the OK and Time taken Executing Hive Queries from Files Hive can execute one or more queries that were saved to a file using the -f file argu- ment.

By convention, saved Hive query files use the. Hive auto- matically looks for a file named. An easy mistake to make is to forget the semicolon at the end of lines like this. When you make this mistake, the definition of the property will include all the text from all the subsequent lines in the file until the next semicolon.

A common source of error and confusion when pasting statements into the CLI occurs where some lines begin with a tab.

Command History You can use the up and down arrow keys to scroll through previous commands. Ac- tually, each previous line of input is shown separately; the CLI does not combine mul- tiline commands and queries into a single history entry. If you want to repeat a previous command, scroll to it and hit Enter. If you want to edit the line before entering it, use the left and right arrow keys to navigate to the point where changes are required and edit the line.

You can hit Return to submit it without returning to the end of the line. Most navigation keystrokes using the Control key work as they do for the bash shell e.

Simply type! Hadoop dfs Commands from Inside Hive You can run the hadoop dfs Comments in Hive Scripts As of Hive v0. The CLI does not parse these comment lines. We can enable this feature by setting the hiveconf property hive.

The Command-Line Interface 39 www. A related concern is how these types are represented in text files, as well as alternatives to text storage that address various performance and other concerns. A unique feature of Hive, compared to most databases, is that it provides great flexibility in how data is encoded in files. Most databases take total control of the data, both how it is persisted to disk and its life cycle.

By letting you control all these aspects, Hive makes it easier to manage and process data with a variety of tools. Primitive Data Types Hive supports several sizes of integer and floating-point types, a Boolean type, and character strings of arbitrary length.

Hive v0. Table lists the primitive types supported by Hive. The character 'Now is the time', "for all set can be specified. Single or double good men" quotes can be used. See discussion below As for other SQL dialects, the case of these names is ignored. Relational databases offer this feature as a performance optimization; fixed-length records are easier to index, scan, etc.

Also, Hadoop and Hive emphasize optimizing disk reading and writing performance, where fixing the lengths of column values is relatively unimportant. BINARY can be used as a way of including arbitrary bytes in a record and preventing Hive from attempting to parse them as numbers, strings, etc.

If a table schema specifies three columns and the data files contain five values for each record, the last two will be ignored by Hive. What if you run a query that wants to interpret a string column as a number? You can explicitly cast one type to another as in the following example, where s is a string column that holds a value representing an integer Collection Data Types Hive supports columns that are structs, maps, and arrays.

Note that the literal syntax examples in Table are actually calls to built-in functions. MAP A collection of key-value tuples, where the fields are accessed map 'first', 'John', using array notation e. For example, if a column name is of type ARRAY of strings with the value ['John', 'Doe'], then the second element can be referenced using name[1].

As for simple types, the case of the type name is ignored. For example, in traditional data models, structs might be captured in separate tables, with foreign key relations between the tables, as appropriate. A practical problem with breaking normal form is the greater risk of data duplication, leading to unnecessary disk space consumption and potential data inconsistencies, as duplicate copies can grow out of sync as changes are made. Collection Data Types 43 www. Embedding collections in records makes re- trieval faster with minimal seeks.

Navigating each foreign key relationship requires seeking across the disk, with significant performance overhead. Employees without subordinates would have an empty array.

In a traditional model, the relationship would go the other way, from an employee to his or her manager. The key is the name of the deduction e. In a traditional data model, there might be separate tables for deduction type each key in our map , where the rows contain particular deduction values and a foreign key pointing back to the corresponding employee record.

Finally, the home address of each employee is represented as a struct, where each field is named and has a particular type. Note that Java syntax conventions for generics are followed for the collection types.

You are no doubt familiar with text files delimited with commas or tabs, the so-called comma-separated values CSVs or tab-separated values TSVs , respectively. However, there is a drawback to both formats; you have to be careful about commas or tabs embedded in text and not intended as field or column delimiters.

For this reason, Hive uses various control characters by default, which are less likely to appear in value strings.

They are listed in Table A text editor like Emacs will show the delimiters this way. Note that the lines have been wrapped in the example because they are too long for the printed page. This might be necessary if another applica- tion writes the data using a different convention. So this clause has limited utility today. So, while you can specify all these clauses explicitly, using the default separators most of the time, you normally only provide the clauses for explicit overrides.

These specifications only affect what Hive expects to see when it reads files. For example, here is a table definition where the data will contain comma-delimited fields. Text File Encoding of Data Values 47 www. They can include a header row with column names and column string values might be quoted and they might contain embedded commas or tabs, respectively.

See Chapter 15 for details on handling these file types more generally. This powerful customization feature makes it much easier to use Hive with files created by other tools and various ETL extract, transform, and load processes.

Schema on Read When you write data to a traditional database, either through loading external data, writing the output of a query, doing UPDATE statements, etc. This is called schema on write.

Hive has no such control over the underlying storage. There are many ways to create, modify, and even damage the data that Hive will query. Therefore, Hive can only en- force queries on read. This is called schema on read. Hive does the best that it can to read the data. If some fields are numbers and Hive encounters nonnumeric strings, it will return nulls for those fields.

Above all else, Hive tries to recover from all errors as best it can. Hive offers no support for row- level inserts, updates, and deletes.

Hive adds ex- tensions to provide better performance in the context of Hadoop and to integrate with custom extensions and even external programs. Still, much of HiveQL will be familiar. This chapter and the ones that follow discuss the features of HiveQL using representative examples. In some cases, we will briefly mention details for completeness, then explore them more fully in later chapters.

This chapter starts with the so-called data definition language parts of HiveQL, which are used for creating, altering, and dropping databases, tables, views, functions, and indexes. Subsequent chapters explore the data manipulation language parts of HiveQL that are used to put data into Hive tables and to extract data to the filesystem, and how to explore and manipulate data with queries, grouping, filtering, joining, etc. Databases in Hive The Hive concept of a database is essentially just a catalog or namespace of tables.

However, they are very useful for larger clusters with multiple teams and users, as a way of avoiding table name collisions.

The simplest syntax for creating a database is shown in the following example: 49 www. The following example lists only those databases that start with the letter h and end with any other characters the. Hive will create a directory for each database. Tables in that database will be stored in subdirectories of the database directory. The database directory is created under a top-level directory specified by the property hive.

Note the. In this example, the URI scheme is hdfs. For a MapR installation, it would be maprfs. You could use s3 as the scheme, but the newer s3n is preferred. If you are running in pseudo-distributed mode, then the master server will be localhost. If the authority is omitted, Hive uses the master-server name and port defined by the property fs. For completeness, when you specify a relative path e.

Unfortunately, there is no command to show you which database is your current working database! When a database is dropped, its directory is also deleted. This is useful in scripts that should create a table the first time they run. However, the clause has a gotcha you should know. You can add a comment to any column, after the type. Like databases, you can attach a comment to the table itself and you can define one or more table properties.

Creating Tables 53 www. A planned enhancement for Hive v0. Finally, you can optionally specify a location for the table data as opposed to meta- data, which the metastore will always hold. The exception is the default database. With no additional arguments, it shows the tables in the current working database. The regular expression in the single quote looks for all tables with names starting with empl and ending with any other characters the. We can drop the mydb. If you only want to see the schema for a particular column, append the column to the table name.

However, they are only shown in the Detailed Table Information if a user-specified table property has also been defined! Managed Tables The tables we have created so far are called managed tables or sometimes called inter- nal tables, because Hive controls the lifecycle of their data more or less.

However, managed tables are less convenient for sharing with other tools. For example, suppose we have data that is created and used primarily by Pig or other tools, but we want to run some queries against it, but not give Hive ownership of the data.

External Tables Suppose we are analyzing data from the stock markets. Therefore, dropping the table does not delete the data, although the metadata for the table will be deleted.

There are a few other small differences between managed and external tables, where some HiveQL constructs are not permitted for external tables. Even for managed tables, you know where they are located, so you can use other tools, hadoop dfs commands, etc.

Still, a general principle of good software design is to express intent. If the data is shared between tools, then creating an external table makes this ownership explicit. Near the end of the Detailed Table Information output, you will see the following for managed tables Creating Tables 57 www.

Hive has the notion of partitioned tables. Our HR people often run queries with WHERE clauses that restrict the results to a particular country or to a particular first-level subdivision e. We have redundant state information in the address field. It is distinct from the state partition. We could remove the state element from address. There is no ambiguity in queries, since we have to use address.

For example Yes, those are the actual directory names. The state directories will contain zero or more files for the employees in those states. In fact, the data just gets in the way in the files, since you have to account for it in the table schema, and this data wastes space. Perhaps the most important reason to partition data is for faster queries. In the previous query, which limits the results to employees in Illinois, it is only necessary to scan the contents of one directory.

Even if we have thousands of country and state directories, all but one can be ignored. For very large data sets, partitioning can dramatically im- prove query performance, but only if the partitioning scheme reflects common range filtering e.

When we add predicates to WHERE clauses that filter on partition values, these predicates are called partition filters. Of course, if you need to do a query for all employees around the globe, you can still do it. Hive will have to read every directory, but hopefully these broader disk scans will be relatively rare. However, a query across all partitions could trigger an enormous MapReduce job if the table data and number of partitions are large. The schema part of the output lists the country and state with the other columns, because they are columns as far as queries are concerned.

The Detailed Table Infor mation includes the country and state as partition keys. The comments for both of these keys are null; we could have added comments just as for regular columns. You create partitions in managed tables by loading data into them. You must specify a value for each partition column. External Partitioned Tables You can use partitioning with external tables. In fact, you may find that this is your most common scenario for managing large production data sets.

You also have more flexibility in the directory structure used, as you define it yourself. Most organ- izations use a standard format for log messages, recording a timestamp, severity e. Suppose our Extract, Transform, and Load ETL process ingests and aggre- gates logfiles in our environment, converting each log message to a tab-delimited record and also decomposing the timestamp into separate year, month, and day fields, and a combined hms field for the remaining hour, minute, and second parts of the timestamp, for reasons that will become clear in a moment.

You could do this parsing of log mes- sages using the string parsing functions built into Hive or Pig, for example. Alterna- tively, we could use smaller integer types for some of the timestamp-related fields to conserve space. Here, we are ignoring subsequent resolution. S3 support is part of the Apache Hadoop distribution. This is convenient when you want to set up partitions before a separate pro- cess starts writing data to them.

As soon as data is there, queries will return results from that data. This feature illustrates another benefit: new data can be written to a dedicated directory with a clear distinction from older data in other directories. As for nonpartitioned external tables, Hive does not own the data and it does not delete the data if the table is dropped.

This output is missing a useful bit of information, the actual location of the partition data. We frequently use external partitioned tables because of the many benefits they pro- vide, such as logical data management, performant queries, etc.

These for- mats are discussed in more detail in Chapter 11 and Chapter Hive draws a distinction between how records are encoded into files and how columns are encoded into records. You customize these behaviors separately. The record encoding is handled by an input format object e. Hive uses a Java class compiled module named org. If you are unfamiliar with Java, the dotted name syn- tax indicates a hierarchical namespace tree of packages that actually corresponds to the directory structure for the Java code.

The last name, TextInputFormat, is a class in the lowest-level package mapred. For completeness, there is also an output format that Hive uses for writing the output of queries to files and to the console. Hive uses an input format to split input streams into records, an output format to format records into output streams i.

Third-party input and output formats and SerDes can be specified, a feature which permits users to customize Hive for a wide range of file formats not supported natively. Hive knows nothing about the meaning of these properties. Note that the name and value of each property must be a quoted string.

If you specify one of these formats, you are required to specify both of them. AvroContainerInputFormat, outputFormat:com. Partitioned, Managed Tables 65 www.

For managed tables, the table metadata and data are deleted. Actually, if you enable the Hadoop Trash feature, which is not on by default, the data is moved to the.

To enable this feature, set the property fs. Trash to the correct directories using the filesystem commands to restore the data. For external tables, the metadata is deleted but the data is not. The data for the table is untouched. X allows you to use the syntax with multiple partition speci- fications, but it actually uses just the first partition specification, silently ignoring the others! For external tables, the data is not deleted. In the example shown, we move the column after the severity column.

As always, this command changes metadata only. If you are moving columns, the data must already match the new schema or you must change it to match by some other means. Alter Table 67 www. See Chapter 15 for more details on SerDes. The following example specifies that a table will use a Java class named com. Note that both the property names e. A typical scenario for this state- ment is to trigger execution of the hooks when table storage files have been modified outside of Hive. Use the appropriate creation commands in that case.

This feature is only available for individual partitions of partitioned tables. Finally, various protections are available. WHERE clauses extensively when we discuss populating tables with data queried from other tables. If they are, please refer to Chapter 6 for details. Or you can just write files in the correct directories by other means.

It is conventional practice to specify a path that is a directory, rather than an individual file. Hive will copy all the files in the directory, which give you the flexibility of organ- izing the data into multiple files and changing the file naming convention, without 71 www.

Either way, the files will be copied to the ap- propriate location for the table and the names will be the same. The data is copied into the final location. In this case, the data is moved from the path to the final location.

Also, because files are moved in this case, Hive requires the source and target files and directories to be in the same filesystem. It is more robust to specify a full path, but relative paths can be used. Without the keyword, the new files are simply added to the target directory.

However, if files already exist in the target directory that match filenames being loaded, the old files are overwritten. Versions of Hive before v0.

Hence, data would be lost. This bug was fixed in the v0. However, it will verify that the file format matches the table definition. This feature is only available in Hive v0. This example suggests one common scenario where this feature is useful: data has been staged in a directory, exposed to Hive as an external table, and now you want to put it into the final, partitioned table. A workflow like this is also useful if you want the target table to have a different record format than the source table e.

Inserting Data into Tables from Queries 73 www. Hence, some records from the input might get written to multiple output locations and others might get dropped!

Fortunately, Hive also supports a dynamic partition feature, where it can infer the partitions to create based on query parameters. By comparison, up until now we have considered only static partitions.

After running this query, employees will have partitions! You can also mix dynamic and static partitions. Dynamic partitioning is not enabled by default. This helps protect against a badly designed query that generates a gigantic number of partitions.

For ex- ample, you partition by timestamp and generate a separate partition for each second! Perhaps you meant to partition by day or maybe hour instead. Several other properties are also used to limit excess resource utilization. Table describes these properties. Dynamic partitions properties Name Default Description hive. Raises a fatal error if one mapper or reducer attempts to create more than the threshold. Raises a fatal error if the limit is exceeded.

A common use for this feature is to extract a convenient subset of data from a larger, more unwieldy table. The specified path can also be a full URI e. Independent of how the data is actually stored in the source table, it is written to files with all fields serialized as strings.

Hive uses the same encoding in the generated output files as it uses for the tables internal storage. If there were two or more reducers writing output, we would have additional files with similar names e. To format columns, the built-in functions include those for formatting strings, such as converting case, padding output, and more. If you export table data frequently, it might be appropriate to use comma or tab delimiters. Unlike many relational databases, there is no temporary table feature in Hive.

Exporting Data 77 www. Of course, we have assumed all along that you have some prior knowledge of SQL. Some special topics will be covered in subsequent chapters. For a given record, SELECT specifies the columns to keep, as well as the outputs of function calls on one or more columns e.

Specify Columns with Regular Expressions We can even use regular expressions to select the columns we want. Computing with Column Values Not only can you select columns in a table, but you can manipulate column values using function calls and arithmetic expressions.

At the time of this writing, the Hive Wiki shows an incorrect syntax for specifying columns using regular expressions. Arithmetic Operators All the typical arithmetic operators are supported.

Table describes the specific details. If the operands are in- teger types, the quotient of the division is returned. Arithmetic operators take any numeric type. No type coercion is performed if the two operands are of the same numeric type. Otherwise, if the types differ, then the value of the smaller of the two types is promoted to wider type of the other value. Wider in the sense that a type with more bytes can hold a wider range of values. Note that our query contained 1 - deductions[…].

You have to be careful about data overflow or underflow when doing arithmetic. Hive follows the rules for the underlying Java types, where no attempt is made to automat- ically convert a result to a wider type if one exists, when overflow or underflow will occur. Multiplication and division are most likely to trigger this problem. It pays to be aware of the ranges of your numeric data values, whether or not those values approach the upper or lower range limits of the types you are using in the cor- responding schema, and what kinds of calculations people might do with the data.

If you are concerned about overflow or underflow, consider using wider types in the schema. The drawback is the extra memory each data value will occupy. Finally, it is sometimes useful to scale data values, such as dividing by powers of 10, using log values, and so on.

Scaling can also improve the accuracy and numerical sta- bility of algorithms used in certain machine learning calculations, for example. Mathematical functions Table describes the built-in mathematical functions, as of Hive v0.

Passing in an integer seed makes the return value deterministic. These functions are the preferred technique, rather than using the cast operator we mentioned above. Aggregate functions A special kind of function is the aggregate function that returns a single value resulting from some computation over many rows.

Perhaps the two best known examples are count, which counts the number of rows or values for a specific column , and avg, which returns the average value of the specified column values. You can usually improve the performance of aggregation by setting the following prop- erty to true, hive. However, this setting will require more memory. To explain by way of an example, the following query converts the subordinate array in each employees record into zero or more new records.

If an employee record has an empty subordinates array, then no new records are generated. When using table gen- erating functions, column aliases are required by Hive. There are many other particular details that you must understand to use these functions correctly. Table lists the built-in table generating functions. Table generating functions Return type Signature Description N rows explode array Return 0 to many rows, one row for each element from the input array. N rows explode map v0.

It takes a URL and the part name1, partname2, …, partna names to extract, returning a tuple. INT length s Return the length of the string. For example, concat 'ab', 'cd' results in 'abcd'. You can pass an arbitrary number of string ar- guments and the result will contain all of them joined together. For example, substr 'abcd', 3 results in 'cd'.

STRING substr s, int start, int Return the substring of s starting from the start posi- length tion with the given length, e. It takes a URL and the partname to extract. A NULL is re- turned if the conversion does not succeed. STRING lpad s, len, pad Returns s exactly len length, prepending instances of the string pad on its left, if necessary, to reach len char- acters. If s is longer than len, it is truncated.

STRING rpad s, len, pad Returns s exactly len length, appending instances of the string pad on its right, if necessary, to reach len char- acters. INT locate substr, str, pos] Returns the index of str after pos where substr is found. INT instr str, substr Returns the index of str where substr is found.