数据库表设计和存储(包括hbase,solr等)

[原创] 数据库表设计和存储(包括hbase,solr等)

仧尐 2018/03/13 126 数据/ solr/ 存储/ hbase

### 一、Hbase内容主表结构和操作
#### 1.1 hbase表[post]创建代码

```mysql
create 'itdaan:post',{NAME=>'a',VERSIONS=>1},{NAME=>'b',VERSIONS=>1},{SPLITS=>['01','02','03','04','05','06','07','08','09','0a','0b','0c','0d','0e','0f','10','11','12','13','14','15','16','17','18','19','1a','1b','1c','1d','1e','1f','20','21','22','23','24','25','26','27','28','29','2a','2b','2c','2d','2e','2f','30','31','32','33','34','35','36','37','38','39','3a','3b','3c','3d','3e','3f','40','41','42','43','44','45','46','47','48','49','4a','4b','4c','4d','4e','4f','50','51','52','53','54','55','56','57','58','59','5a','5b','5c','5d','5e','5f','60','61','62','63','64','65','66','67','68','69','6a','6b','6c','6d','6e','6f','70','71','72','73','74','75','76','77','78','79','7a','7b','7c','7d','7e','7f','80','81','82','83','84','85','86','87','88','89','8a','8b','8c','8d','8e','8f','90','91','92','93','94','95','96','97','98','99','9a','9b','9c','9d','9e','9f','a0','a1','a2','a3','a4','a5','a6','a7','a8','a9','aa','ab','ac','ad','ae','af','b0','b1','b2','b3','b4','b5','b6','b7','b8','b9','ba','bb','bc','bd','be','bf','c0','c1','c2','c3','c4','c5','c6','c7','c8','c9','ca','cb','cc','cd','ce','cf','d0','d1','d2','d3','d4','d5','d6','d7','d8','d9','da','db','dc','dd','de','df','e0','e1','e2','e3','e4','e5','e6','e7','e8','e9','ea','eb','ec','ed','ee','ef','f0','f1','f2','f3','f4','f5','f6','f7','f8','f9','fa','fb','fc','fd','fe','ff']}
```

#### 1.2 hbase表[post]字段描述

| 列族     | 列名         | 备注       |
| ------ | ---------- | -------- |
| rowkey | md5(url)   | url的md5值 |
| a      | title      | 标题       |
| a      | content    | 内容       |
| a      | author     | 作者       |
| a      | postTime   | 发布时间     |
| a      | url        | 地址       |
| a      | tags       | 标签       |
| a      | webId      | 网站ID     |
| a      | typeId     | 网站类型ID   |
| a      | viewNum    | 查看次数     |
| a      | isCrowd    | 是否爬取成功   |
| a      | crowdTimes | 失败次数     |
| a      | replies    | 回复内容     |
| a      | crowdTime  | 爬取时间     |

#### 1.3 hbase表[post]标签映射hive表[post]结构

```mysql
    CREATE EXTERNAL TABLE if not exists post(
             id  string,
             title string,
             titleZH string,
             content  string,
             author string,
             postTime string,
             url string,
             tags string,
             webId string,
             typeId string,
             viewNum int,
             voteNum int,
             commentNum int,
             upNum int,
             downNum int,
             crowdTimes string,
             `replies` string,
             csdn int,
             crowdTime string,
             publish string,
             updateTime string,
             updatetimes bigint
      )
     STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler' 
     WITH SERDEPROPERTIES('hbase.columns.mapping' = ':key,a:title,a:titleZH,a:content,a:author,a:postTime,a:url,a:tags,a:webId,a:typeId,a:viewNum,a:voteNum,a:commentNum,a:upNum,a:downNum,a:crowdTimes,a:replies,a:csdn,a:crowdTime,a:publish,a:updateTime,:timestamp')
     TBLPROPERTIES('hbase.table.name' = 'itdaan:post');
```

#### 1.4 hive表[solr_post]映射solr表结构

```mysql
add jar /data/soft/hive/lib/Hive2Solr-2.1.1-5.5.3.jar;
drop table if exists solr_post;
create external table solr_post (
  id string,
  title string,
  text string,
  author string,
  url string,
  tags string,
  web_id int,
  type_id int,
  vote_num int,
  view_num int,
  comment_num int,
  post_time string
) 
stored by "com.jazz.hive.store.SolrStorageHandler"
tblproperties('solr.url' = 'http://localhost:8983/solr/contentindex',
                    'solr.query' = '*:*',
                    'solr.cursor.batch.size'='1000',
                    'solr.primary_key'='id' ,
                    'is.solrcloud'='0'
                    );
```

#### 1.5 使用solr_post创建solr索引

```mysql
INSERT OVERWRITE TABLE solr_post select id,title,content as `text`,author,url,if(tags is null,"",concat("_array_",tags)) as tags,webId,typeId,if(voteNum is null,100,voteNum) as voteNum,if(viewNum is null,0,viewNum) as viewNum,if(commentNum is null,0,commentNum) as commentNum,(case when length(postTime) == 10 then from_unixtime(unix_timestamp(postTime,'yyyy-MM-dd'),"yyyy-MM-dd'T'HH:mm:ss'Z'") else from_unixtime(unix_timestamp(postTime,'yyyy-MM-dd HH:mm:ss'),"yyyy-MM-dd'T'HH:mm:ss'Z'") end) as postTime from post where publish is null or publish = '1'
```

### 二、相同特点的文章ID映射表
#### 2.1 标题相同映射表[title_id]
```sql
create 'itdaan:title_id',{NAME=>'a',VERSIONS=>1},{SPLITS=>['01','02','03','04','05','06','07','08','09','0a','0b','0c','0d','0e','0f','10','11','12','13','14','15','16','17','18','19','1a','1b','1c','1d','1e','1f','20','21','22','23','24','25','26','27','28','29','2a','2b','2c','2d','2e','2f','30','31','32','33','34','35','36','37','38','39','3a','3b','3c','3d','3e','3f','40','41','42','43','44','45','46','47','48','49','4a','4b','4c','4d','4e','4f','50','51','52','53','54','55','56','57','58','59','5a','5b','5c','5d','5e','5f','60','61','62','63','64','65','66','67','68','69','6a','6b','6c','6d','6e','6f','70','71','72','73','74','75','76','77','78','79','7a','7b','7c','7d','7e','7f','80','81','82','83','84','85','86','87','88','89','8a','8b','8c','8d','8e','8f','90','91','92','93','94','95','96','97','98','99','9a','9b','9c','9d','9e','9f','a0','a1','a2','a3','a4','a5','a6','a7','a8','a9','aa','ab','ac','ad','ae','af','b0','b1','b2','b3','b4','b5','b6','b7','b8','b9','ba','bb','bc','bd','be','bf','c0','c1','c2','c3','c4','c5','c6','c7','c8','c9','ca','cb','cc','cd','ce','cf','d0','d1','d2','d3','d4','d5','d6','d7','d8','d9','da','db','dc','dd','de','df','e0','e1','e2','e3','e4','e5','e6','e7','e8','e9','ea','eb','ec','ed','ee','ef','f0','f1','f2','f3','f4','f5','f6','f7','f8','f9','fa','fb','fc','fd','fe','ff']}
```
#### 2.2 前100个字符相同映射表[start_id]
```sql
create 'itdaan:start_id',{NAME=>'a',VERSIONS=>1},{SPLITS=>['01','02','03','04','05','06','07','08','09','0a','0b','0c','0d','0e','0f','10','11','12','13','14','15','16','17','18','19','1a','1b','1c','1d','1e','1f','20','21','22','23','24','25','26','27','28','29','2a','2b','2c','2d','2e','2f','30','31','32','33','34','35','36','37','38','39','3a','3b','3c','3d','3e','3f','40','41','42','43','44','45','46','47','48','49','4a','4b','4c','4d','4e','4f','50','51','52','53','54','55','56','57','58','59','5a','5b','5c','5d','5e','5f','60','61','62','63','64','65','66','67','68','69','6a','6b','6c','6d','6e','6f','70','71','72','73','74','75','76','77','78','79','7a','7b','7c','7d','7e','7f','80','81','82','83','84','85','86','87','88','89','8a','8b','8c','8d','8e','8f','90','91','92','93','94','95','96','97','98','99','9a','9b','9c','9d','9e','9f','a0','a1','a2','a3','a4','a5','a6','a7','a8','a9','aa','ab','ac','ad','ae','af','b0','b1','b2','b3','b4','b5','b6','b7','b8','b9','ba','bb','bc','bd','be','bf','c0','c1','c2','c3','c4','c5','c6','c7','c8','c9','ca','cb','cc','cd','ce','cf','d0','d1','d2','d3','d4','d5','d6','d7','d8','d9','da','db','dc','dd','de','df','e0','e1','e2','e3','e4','e5','e6','e7','e8','e9','ea','eb','ec','ed','ee','ef','f0','f1','f2','f3','f4','f5','f6','f7','f8','f9','fa','fb','fc','fd','fe','ff']}
```
#### 2.3 后100个字符相同的映射表[end_id]
```sql
create 'itdaan:end_id',{NAME=>'a',VERSIONS=>1},{SPLITS=>['01','02','03','04','05','06','07','08','09','0a','0b','0c','0d','0e','0f','10','11','12','13','14','15','16','17','18','19','1a','1b','1c','1d','1e','1f','20','21','22','23','24','25','26','27','28','29','2a','2b','2c','2d','2e','2f','30','31','32','33','34','35','36','37','38','39','3a','3b','3c','3d','3e','3f','40','41','42','43','44','45','46','47','48','49','4a','4b','4c','4d','4e','4f','50','51','52','53','54','55','56','57','58','59','5a','5b','5c','5d','5e','5f','60','61','62','63','64','65','66','67','68','69','6a','6b','6c','6d','6e','6f','70','71','72','73','74','75','76','77','78','79','7a','7b','7c','7d','7e','7f','80','81','82','83','84','85','86','87','88','89','8a','8b','8c','8d','8e','8f','90','91','92','93','94','95','96','97','98','99','9a','9b','9c','9d','9e','9f','a0','a1','a2','a3','a4','a5','a6','a7','a8','a9','aa','ab','ac','ad','ae','af','b0','b1','b2','b3','b4','b5','b6','b7','b8','b9','ba','bb','bc','bd','be','bf','c0','c1','c2','c3','c4','c5','c6','c7','c8','c9','ca','cb','cc','cd','ce','cf','d0','d1','d2','d3','d4','d5','d6','d7','d8','d9','da','db','dc','dd','de','df','e0','e1','e2','e3','e4','e5','e6','e7','e8','e9','ea','eb','ec','ed','ee','ef','f0','f1','f2','f3','f4','f5','f6','f7','f8','f9','fa','fb','fc','fd','fe','ff']}
```
#### 2.4 相似度高重复的文章的ID映射表[repeat_id]
```sql
create 'itdaan:repeat_id',{NAME=>'a',VERSIONS=>1},{SPLITS=>['01','02','03','04','05','06','07','08','09','0a','0b','0c','0d','0e','0f','10','11','12','13','14','15','16','17','18','19','1a','1b','1c','1d','1e','1f','20','21','22','23','24','25','26','27','28','29','2a','2b','2c','2d','2e','2f','30','31','32','33','34','35','36','37','38','39','3a','3b','3c','3d','3e','3f','40','41','42','43','44','45','46','47','48','49','4a','4b','4c','4d','4e','4f','50','51','52','53','54','55','56','57','58','59','5a','5b','5c','5d','5e','5f','60','61','62','63','64','65','66','67','68','69','6a','6b','6c','6d','6e','6f','70','71','72','73','74','75','76','77','78','79','7a','7b','7c','7d','7e','7f','80','81','82','83','84','85','86','87','88','89','8a','8b','8c','8d','8e','8f','90','91','92','93','94','95','96','97','98','99','9a','9b','9c','9d','9e','9f','a0','a1','a2','a3','a4','a5','a6','a7','a8','a9','aa','ab','ac','ad','ae','af','b0','b1','b2','b3','b4','b5','b6','b7','b8','b9','ba','bb','bc','bd','be','bf','c0','c1','c2','c3','c4','c5','c6','c7','c8','c9','ca','cb','cc','cd','ce','cf','d0','d1','d2','d3','d4','d5','d6','d7','d8','d9','da','db','dc','dd','de','df','e0','e1','e2','e3','e4','e5','e6','e7','e8','e9','ea','eb','ec','ed','ee','ef','f0','f1','f2','f3','f4','f5','f6','f7','f8','f9','fa','fb','fc','fd','fe','ff']}
```
#### 2.5 repeat_id的hive映射表
```sql
CREATE EXTERNAL TABLE if not exists repeat_id(
         key string,
         id  string
  )
 STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler' 
 WITH SERDEPROPERTIES('hbase.columns.mapping' = ':key,a:id')
 TBLPROPERTIES('hbase.table.name' = 'itdaan:repeat_id');
```

注意！

本站用户原创文章，版权为本站用户，转摘请注明本文地址：http://www.silva-art.net/blog/2018/03/13/c6b79565f1bc33bd9af46719072d2cff.html。

猜您在找

分布式数据库HBase表设计 HBase 是列式存储数据库吗树形结构的数据存储和数据库表设计 Java在HBase数据库创建表 Extjs4 权限系统(包括页面按钮权限) 设计思路+数据库表设计