Tom White - Hadoop The Definitive Guide_ 4 edition - 2015 (811394), страница 101
Текст из файла (страница 101)
The fields must all be of array(1, 2) athe same type.MAPAn unordered collection of key-value pairs. Keys mustbe primitives; values may be any type. For a particularmap, the keys must be the same type, and the valuesmust be the same type.map('a', 1, 'b', 2)STRUCTA collection of named fields. The fields may be ofdifferent types.struct('a', 1, 1.0),bnamed_struct('col1', 'a','col2', 1, 'col3', 1.0)UNIONA value that may be one of a number of defined datatypes.
The value is tagged with an integer (zeroindexed) representing its data type in the union.create_union(1, 'a', 63)a The literal forms for arrays, maps, structs, and unions are provided as functions. That is, array, map, struct, and create_union are built-in Hive functions.b The columns are named col1, col2, col3, etc.Primitive typesHive’s primitive types correspond roughly to Java’s, although some names are influencedby MySQL’s type names (some of which, in turn, overlap with SQL-92’s).
There is aBOOLEAN type for storing true and false values. There are four signed integral types:TINYINT, SMALLINT, INT, and BIGINT, which are equivalent to Java’s byte, short, int,and long primitive types, respectively (they are 1-byte, 2-byte, 4-byte, and 8-byte signedintegers).Hive’s floating-point types, FLOAT and DOUBLE, correspond to Java’s float and double,which are 32-bit and 64-bit floating-point numbers.The DECIMAL data type is used to represent arbitrary-precision decimals, like Java’sBigDecimal, and are commonly used for representing currency values. DECIMAL valuesare stored as unscaled integers.
The precision is the number of digits in the unscaledvalue, and the scale is the number of digits to the right of the decimal point. So, forexample, DECIMAL(5,2) stores numbers between −999.99 and 999.99. If the scale isomitted then it defaults to 0, so DECIMAL(5)stores numbers in the range −99,999 to99,999 (i.e., integers). If the precision is omitted then it defaults to 10, so DECIMAL isequivalent to DECIMAL(10,0). The maximum allowed precision is 38, and the scale mustbe no larger than the precision.There are three Hive data types for storing text.
STRING is a variable-length characterstring with no declared maximum length. (The theoretical maximum size STRING thatmay be stored is 2 GB, although in practice it may be inefficient to materialize such largevalues. Sqoop has large object support; see “Importing Large Objects” on page 415.)VARCHAR types are similar except they are declared with a maximum length between 1HiveQL|487and 65355; for example, VARCHAR(100). CHAR types are fixed-length strings that arepadded with trailing spaces if necessary; for example, CHAR(100). Trailing spaces areignored for the purposes of string comparison of CHAR values.The BINARY data type is for storing variable-length binary data.The TIMESTAMP data type stores timestamps with nanosecond precision.
Hive comeswith UDFs for converting between Hive timestamps, Unix timestamps (seconds sincethe Unix epoch), and strings, which makes most common date operations tractable.TIMESTAMP does not encapsulate a time zone; however, the to_utc_timestamp andfrom_utc_timestamp functions make it possible to do time zone conversions.The DATE data type stores a date with year, month, and day components.Complex typesHive has four complex types: ARRAY, MAP, STRUCT, and UNION. ARRAY and MAP are liketheir namesakes in Java, whereas a STRUCT is a record type that encapsulates a set ofnamed fields.
A UNION specifies a choice of data types; values must match exactly oneof these types.Complex types permit an arbitrary level of nesting. Complex type declarations mustspecify the type of the fields in the collection, using an angled bracket notation, asillustrated in this table definition with three columns (one for each complex type):CREATE TABLE complex (c1 ARRAY<INT>,c2 MAP<STRING, INT>,c3 STRUCT<a:STRING, b:INT, c:DOUBLE>,c4 UNIONTYPE<STRING, INT>);If we load the table with one row of data for ARRAY, MAP, STRUCT, and UNION, as shownin the “Literal examples” column in Table 17-3 (we’ll see the file format needed to dothis in “Storage Formats” on page 496), the following query demonstrates the field accessoroperators for each type:hive> SELECT c1[0], c2['b'], c3.c, c4 FROM complex;121.0{1:63}Operators and FunctionsThe usual set of SQL operators is provided by Hive: relational operators (such as x ='a' for testing equality, x IS NULL for testing nullity, and x LIKE 'a%' for patternmatching), arithmetic operators (such as x + 1 for addition), and logical operators (suchas x OR y for logical OR).
The operators match those in MySQL, which deviates fromSQL-92 because || is logical OR, not string concatenation. Use the concat function forthe latter in both MySQL and Hive.488|Chapter 17: HiveHive comes with a large number of built-in functions—too many to list here—dividedinto categories that include mathematical and statistical functions, string functions, datefunctions (for operating on string representations of dates), conditional functions, ag‐gregate functions, and functions for working with XML (using the xpath function) andJSON.You can retrieve a list of functions from the Hive shell by typing SHOW FUNCTIONS.4 Toget brief usage instructions for a particular function, use the DESCRIBE command:hive> DESCRIBE FUNCTION length;length(str | binary) - Returns the length of str or number of bytes in binarydataIn the case when there is no built-in function that does what you want, you can writeyour own; see “User-Defined Functions” on page 510.ConversionsPrimitive types form a hierarchy that dictates the implicit type conversions Hive willperform in function and operator expressions.
For example, a TINYINT will be convertedto an INT if an expression expects an INT; however, the reverse conversion will not occur,and Hive will return an error unless the CAST operator is used.The implicit conversion rules can be summarized as follows. Any numeric type can beimplicitly converted to a wider type, or to a text type (STRING, VARCHAR, CHAR). All thetext types can be implicitly converted to another text type.
Perhaps surprisingly, theycan also be converted to DOUBLE or DECIMAL. BOOLEAN types cannot be converted to anyother type, and they cannot be implicitly converted to any other type in expressions.TIMESTAMP and DATE can be implicitly converted to a text type.You can perform explicit type conversion using CAST. For example, CAST('1' AS INT)will convert the string '1' to the integer value 1. If the cast fails—as it does inCAST('X' AS INT), for example—the expression returns NULL.TablesA Hive table is logically made up of the data being stored and the associated metadatadescribing the layout of the data in the table.
The data typically resides in HDFS, al‐though it may reside in any Hadoop filesystem, including the local filesystem or S3.Hive stores the metadata in a relational database and not in, say, HDFS (see “The Met‐astore” on page 480).In this section, we look in more detail at how to create tables, the different physicalstorage formats that Hive offers, and how to import data into tables.4.
Or see the Hive function reference.Tables|489Multiple Database/Schema SupportMany relational databases have a facility for multiple namespaces, which allows usersand applications to be segregated into different databases or schemas. Hive supports thesame facility and provides commands such as CREATE DATABASE dbname, USE dbname,and DROP DATABASE dbname. You can fully qualify a table by writing dbname.tablename. If no database is specified, tables belong to the default database.Managed Tables and External TablesWhen you create a table in Hive, by default Hive will manage the data, which meansthat Hive moves the data into its warehouse directory. Alternatively, you may create anexternal table, which tells Hive to refer to the data that is at an existing location outsidethe warehouse directory.The difference between the two table types is seen in the LOAD and DROP semantics.
Let’sconsider a managed table first.When you load data into a managed table, it is moved into Hive’s warehouse directory.For example, this:CREATE TABLE managed_table (dummy STRING);LOAD DATA INPATH '/user/tom/data.txt' INTO table managed_table;will move the file hdfs://user/tom/data.txt into Hive’s warehouse directory for themanaged_table table, which is hdfs://user/hive/warehouse/managed_table.5The load operation is very fast because it is just a move or re‐name within a filesystem. However, bear in mind that Hive doesnot check that the files in the table directory conform to the sche‐ma declared for the table, even for managed tables. If there is amismatch, this will become apparent at query time, often by thequery returning NULL for a missing field.
You can check that thedata is being parsed correctly by issuing a simple SELECT state‐ment to retrieve a few rows directly from the table.If the table is later dropped, using:DROP TABLE managed_table;5. The move will succeed only if the source and target filesystems are the same. Also, there is a special case whenthe LOCAL keyword is used, where Hive will copy the data from the local filesystem into Hive’s warehousedirectory (even if it, too, is on the same local filesystem). In all other cases, though, LOAD is a move operationand is best thought of as such.490|Chapter 17: Hivethe table, including its metadata and its data, is deleted. It bears repeating that since theinitial LOAD performed a move operation, and the DROP performed a delete operation,the data no longer exists anywhere.
This is what it means for Hive to manage the data.An external table behaves differently. You control the creation and deletion of the data.The location of the external data is specified at table creation time:CREATE EXTERNAL TABLE external_table (dummy STRING)LOCATION '/user/tom/external_table';LOAD DATA INPATH '/user/tom/data.txt' INTO TABLE external_table;With the EXTERNAL keyword, Hive knows that it is not managing the data, so it doesn’tmove it to its warehouse directory.
![](https://s.studizba.com/z.php?f=/templates/si/i/noavatar.png&w=100&h=100&t=1"){kind=avatar}
