向含基本与复杂数据类型的Hive表插入数据：Student1表操作咨询

Hey there! Let's walk through how to insert data into your student1 table that includes both basic and complex data types. I'll cover a couple of common, practical methods below:

You can use the INSERT INTO ... VALUES syntax to add rows directly. The key here is correctly formatting the struct and map complex types—this is usually the tricky part!

INSERT INTO student1
VALUES
  -- First student record
  (1, 'Alice Smith', STRUCT(123, 'Main Street', 10001), MAP('Math', 95, 'English', 88)),
  -- Second student record
  (2, 'Bob Johnson', STRUCT(456, 'Oak Avenue', 10002), MAP('Math', 78, 'Science', 92));

A quick breakdown of the complex type syntax:

• For the address struct: Use STRUCT() and pass values in the exact order of its definition (a:int, b:string, c:int). So first is the integer a, second is the string b, third is integer c.
• For the marks map: Use MAP() with comma-separated key-value pairs. Keys are strings (subject names), values are integers (scores).

If you have data stored in another Hive table, you can transform and insert it by constructing the complex types in a SELECT statement. For example, suppose you have a temp_student table with raw, unstructured columns:

INSERT INTO student1
SELECT
  temp_s_id,
  temp_s_name,
  -- Build the struct from individual columns
  STRUCT(temp_a, temp_b, temp_c) AS address,
  -- Build the map from subject score columns
  MAP('Math', temp_math_mark, 'English', temp_english_mark) AS marks
FROM temp_student;

Quick Tips to Avoid Errors #

• Type Matching: Double-check that all values match the table's schema. Don't pass a string to the a field of the struct (it expects an integer), and make sure map values are integers, not strings.
• Varchar Limits: The s_name field is varchar(100)—any name longer than 100 characters will get truncated automatically, so keep names within that length.
• Overwrite vs Append: Use INSERT OVERWRITE instead of INSERT INTO if you want to replace all existing data in the table (instead of adding new rows to it).

内容的提问来源于stack exchange，提问作者M Hossain