2013-12-21

Sqoop

• <參考> ​MySQL 新增使用者

  for ((i=1;i<=30;i++)); do printf "CREATE DATABASE user%02d;\n" $i; done > add_user_db.sql
  mysql -h 主機名稱 -u root -p < add_user_db.sql
  for ((i=1;i<=30;i++)); do printf "GRANT SELECT,INSERT,UPDATE ON user%02d.* TO 'user%02d'@'%s' IDENTIFIED BY '密碼';\n" $i $i '%'; done > add_user.sql
  echo "FLUSH PRIVILEGES;" >> add_user.sql
  mysql -h 主機名稱 -u root -p < add_user.sql
  

• 建立 MySQL 測試資料庫

  mysql> use test;
  mysql> show tables;
  mysql> create table mysql_data(id int primary key, name varchar(50));
  mysql> show tables;
  

  mysql> show tables;
  +----------------+
  | Tables_in_test |
  +----------------+
  | mysql_data     |
  +----------------+
  1 row in set (0.01 sec)
  

  mysql> insert into mysql_data values (1, 'Hello');
  mysql> insert into mysql_data values (2, 'World');
  

  mysql> select * from mysql_data;
  +----+-------+
  | id | name  |
  +----+-------+
  |  1 | Hello |
  |  2 | World |
  +----+-------+
  2 rows in set (0.01 sec)資料庫名
  

• 建立測試帳號

  mysql> GRANT SELECT,INSERT,UPDATE ON 資料庫名.* TO user@host IDENTIFIED BY '密碼';
  mysql> FLUSH PRIVILEGES;
  

• <參考> ​將 CSV 檔匯入 MySQL 資料庫 -- 用 Command Line

  mysqlimport -u root -p --lines-terminated-by="\r\n" --fields-terminated-by="," Dbname csvfile.csv
  

• ​http://dev.mysql.com/doc/refman/5.0/en/load-data-local.html
  • If you use LOAD DATA LOCAL in Perl scripts or other programs that read the [client] group from option files, you can add the local-infile=1 option to that group. However, to keep this from causing problems for programs that do not understand local-infile, specify it using the loose- prefix:

        [client]
        loose-local-infile=1
    

  • If LOAD DATA LOCAL is disabled, either in the server or the client, a client that attempts to issue such a statement receives the following error message:

        ERROR 1148: The used command is not allowed with this MySQL version
    

• 執行 Sqoop 連到 MySQL 匯入資料的測試

  jazz@master ~ $ sqoop import --connect jdbc:mysql://sql.3du.me/test --table mysql_data --username 帳號 -P
  jazz@master ~ $ hadoop fs -cat mysql_data/part-m-00000
  jazz@master ~ $ sqoop import --connect jdbc:mysql://sql.3du.me/test --table nyse_daily --username 帳號 -P -m 1
  jazz@master ~ $ hadoop fs -cat nyse_daily/part-m-00000 | wc
  

  wget http://download.microsoft.com/download/0/9/E/09E1D1A1-79F6-4BC2-969B-406EC77C7277/sqljdbc_4.0.2206.100_cht.tar.gz
  tar zxvf sqljdbc_4.0.2206.100_cht.tar.gz
  

• <參考> ​Adding an administrator user to SQL Server 2008

  CREATE LOGIN [LoginName] WITH PASSWORD = 'SomePassword' ;
  

• <參考> ​sp_changedbowner (Transact-SQL) - 修改資料庫的預設擁有者

  use [DatabaseName]; EXEC sp_changedbowner [LoginName];
  

• <參考>

  ALTER LOGIN [LoginName] WITH DEFAULT_DATABASE = [DatabaseName]
  

• <參考> ​BULK INSERT (Transact-SQL) - 將 CSV 文字檔匯入到資料表

  BULK INSERT NYSE_DAILY FROM 'c:\NYSE_daily.txt' WITH ( FIELDTERMINATOR ='\t', ROWTERMINATOR ='\n' );
  

  jazz@master ~ $ sqoop import --connect "jdbc:sqlserver://sql.3du.me;database=user30" --table mysql_data --username user30 -P