<commit_before>---
layout: post
title: Hive, Sqoop - 17/01/24
category: acorn수업
---

## 증권데이터문제
NASDAQ_daily_prices_A_sample.csv, NASDAQ_dividends_A.csv 다운로드  

> [daily]  
field명  
exchange, stock_symbol, date,  
stock_price_open, stock_price_high, stock_price_low, stock_price_close,  
stock_volume, stock_price_adj_close  


> [dividends]  
field명  
exchange, stock_symbol, date, dividends  


1. NASDAQ daily data set을 위한 external table을 생성하시오.
  ```
  > CREATE EXTERNAL TABLE daily_ex
    (exchange_1 STRING, stock_symbol STRING, date_1 STRING, stock_price_open FLOAT, 
     stock_price_high FLOAT, stock_price_low FLOAT, stock_price_close FLOAT, 
     stock_volume BIGINT, stock_price_adj_close FLOAT) 
    ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' 
    STORED AS TEXTFILE LOCATION '/user/hadoop/daily_ex';
    
  > LOAD DATA LOCAL INPATH '/home/hadoop/practice3_hive_test/NASDAQ_daily_prices_A_sample.csv' 
    OVERWRITE INTO TABLE daily_ex;
  ```

2. NASDAQ dividends data set(배당금)을 위한 external table을 생성하시오.
  ```
  > CREATE EXTERNAL TABLE dividends_ex
    (exchange_2 STRING, stock_symbol STRING, date_2 STRING, dividends FLOAT) 
    ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' 
    STORED AS TEXTFILE LOCATION '/user/hadoop/dividends_ex';
    
  > LOAD DATA LOCAL INPATH '/home/hadoop/practice3_hive_test/NASDAQ_dividends_A.csv' 
    OVERWRITE INTO TABLE dividends_ex;
  ```

3. 종가가 5$이상인 종목의 총거래량에 대한 값을 출력하시오.
  ```
  > select stock_price_close, sum(stock_volume) 
    from daily_ex 
    where stock_price_close > 5.0;
    
  > select stock_symbol, sum(stock_volume) 
    from daily_ex 
    where stock_price_close >5.0 <commit_msg>Create 2017-05-25-acorn_day58_170124.md<commit_after>---
layout: post
title: Hive, Sqoop - 17/01/24
category: acorn수업
---

## 증권데이터문제
NASDAQ_daily_prices_A_sample.csv, NASDAQ_dividends_A.csv 다운로드  

> [daily]  
field명  
exchange, stock_symbol, date,  
stock_price_open, stock_price_high, stock_price_low, stock_price_close,  
stock_volume, stock_price_adj_close  


> [dividends]  
field명  
exchange, stock_symbol, date, dividends  


1. NASDAQ daily data set을 위한 external table을 생성하시오.
  ```
  > CREATE EXTERNAL TABLE daily_ex
    (exchange_1 STRING, stock_symbol STRING, date_1 STRING, stock_price_open FLOAT, 
     stock_price_high FLOAT, stock_price_low FLOAT, stock_price_close FLOAT, 
     stock_volume BIGINT, stock_price_adj_close FLOAT) 
    ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' 
    STORED AS TEXTFILE LOCATION '/user/hadoop/daily_ex';

  > LOAD DATA LOCAL INPATH '/home/hadoop/practice3_hive_test/NASDAQ_daily_prices_A_sample.csv' 
    OVERWRITE INTO TABLE daily_ex;
  ```

2. NASDAQ dividends data set(배당금)을 위한 external table을 생성하시오.
  ```
  > CREATE EXTERNAL TABLE dividends_ex
    (exchange_2 STRING, stock_symbol STRING, date_2 STRING, dividends FLOAT) 
    ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' 
    STORED AS TEXTFILE LOCATION '/user/hadoop/dividends_ex';

  > LOAD DATA LOCAL INPATH '/home/hadoop/practice3_hive_test/NASDAQ_dividends_A.csv' 
    OVERWRITE INTO TABLE dividends_ex;
  ```

3. 종가가 5$이상인 종목의 총거래량에 대한 값을 출력하시오.
  ```
  > select stock_price_close, sum(stock_volume) 
    from daily_ex 
    where stock_price_close > 5.0;

  > select stock_symbol, sum(stock_volume) 
    from daily_ex 
    where stock_price_close >5.0 
{'fraction_non_alphanumeric': 0.07962529274004684, 'fraction_numerical': 0.01639344262295082, 'mean_word_length': 4.025, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 23, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '5549465', 'n_tokens_mistral': 1454, 'n_tokens_neox': 1432, 'n_words': 309}