Doris相关问题处理和异常处理

1、decimal 字段异常

 修改为

2、连接超时

Caused by: com.mysql.cj.exceptions.CJCommunicationsException: Communications link failure
The last packet successfully received from the server was 1,068 milliseconds ago. The last packet sent successfully to the server was 1,068 milliseconds ago.
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
	at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:61)
	at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:105)
	at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:151)
	at com.mysql.cj.exceptions.ExceptionFactory.createCommunicationsException(ExceptionFactory.java:167)
	at com.mysql.cj.protocol.a.NativeProtocol.readMessage(NativeProtocol.java:520)
	at com.mysql.cj.protocol.a.NativeProtocol.checkErrorMessage(NativeProtocol.java:700)
	at com.mysql.cj.protocol.a.NativeProtocol.sendCommand(NativeProtocol.java:639)
	at com.mysql.cj.protocol.a.NativeProtocol.sendQueryPacket(NativeProtocol.java:987)
	at com.mysql.cj.NativeSession.execSQL(NativeSession.java:666)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:930)
	... 18 common frames omitted
Caused by: java.net.SocketException: Socket closed
	at java.net.SocketInputStream.socketRead0(Native Method)
	at java.net.SocketInputStream.socketRead(SocketInputStream.java:116)
	at java.net.SocketInputStream.read(SocketInputStream.java:171)
	at java.net.SocketInputStream.read(SocketInputStream.java:141)
	at com.mysql.cj.protocol.ReadAheadInputStream.fill(ReadAheadInputStream.java:107)
	at com.mysql.cj.protocol.ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(ReadAheadInputStream.java:150)
	at com.mysql.cj.protocol.ReadAheadInputStream.read(ReadAheadInputStream.java:180)
	at java.io.FilterInputStream.read(FilterInputStream.java:133)
	at com.mysql.cj.protocol.FullReadInputStream.readFully(FullReadInputStream.java:64)
	at com.mysql.cj.protocol.a.SimplePacketReader.readHeaderLocal(SimplePacketReader.java:81)
	at com.mysql.cj.protocol.a.SimplePacketReader.readHeader(SimplePacketReader.java:63)
	at com.mysql.cj.protocol.a.SimplePacketReader.readHeader(SimplePacketReader.java:45)
	at com.mysql.cj.protocol.a.TimeTrackingPacketReader.readHeader(TimeTrackingPacketReader.java:52)
	at com.mysql.cj.protocol.a.TimeTrackingPacketReader.readHeader(TimeTrackingPacketReader.java:41)
	at com.mysql.cj.protocol.a.MultiPacketReader.readHeader(MultiPacketReader.java:54)
	at com.mysql.cj.protocol.a.MultiPacketReader.readHeader(MultiPacketReader.java:44)
	at com.mysql.cj.protocol.a.NativeProtocol.readMessage(NativeProtocol.java:514)
	... 23 common frames omitted

3、doris修改表名

alter table dws_monthly_future_forecast rename dws_monthly_future_forecast_back;

4、内存超限制

SHOW VARIABLES LIKE "%mem_limit%";

MySQL [dorisonline]> SHOW VARIABLES LIKE "%mem_limit%";
+------------------------------------------+------------+
| Variable_name                            | Value      |
+------------------------------------------+------------+
| broadcast_hashtable_mem_limit_percentage | 0.2        |
| exec_mem_limit                           | 2147483648 |
+------------------------------------------+------------+
2 rows in set (0.00 sec)
SET GLOBAL exec_mem_limit = 8589934592;

SET GLOBAL exec_mem_limit = 4294967296;

245 内存已不足
新增了247节点,重新建表  执行成功

5、修改表字段类型

  • 修改列类型
  • alter table example_tbl modify column k3 varchar(50) key null comment 'to 50'
  • alter table ads_actual_forecast_sales_ratio modify column `future_1_month_sale` decimalv3(25, 12) NULL COMMENT '预测未来一月销售金额';
    alter table ads_actual_forecast_sales_ratio modify column `total_money_month` decimalv3(25, 12) NULL COMMENT '实际销售金额';
    alter table ads_actual_forecast_sales_ratio modify column `actual_forecast_sales_ratio` decimalv3(25, 12) NULL COMMENT '预测和实际销售金额占比';

alter table ads_actual_forecast_sales_ratio modify column `future_1_month_sale` decimalv3(25, 12) NULL COMMENT '预测未来一月销售金额';

  alter table dws_new_customers modify column `in_to_first_datediff_time` int(20) comment '录入到首单时间差';

6、doris where 中的条件判断

     IF(判断语句,v1,v2)

select dt,
ym,
(year(dt) - year(cast(concat(ym, '-01') as date))) * 12 + month(dt) -
month(cast(concat(ym, '-01') as date)) month_diff,
total_money_month
from ads_sale_money_contract_analyse_month
where dt = '2023-08-31' and IF('2023-08-31' = LAST_DAY('2023-08-31'), ym <= '2023-08', ym < '2023-08')

7、修改表名

ALTER TABLE table1 RENAME table2;

ALTER TABLE ads_sale_money_contract_analyse_month RENAME ads_sale_money_contract_analyse_month_back;

8、timeout when waiting for send fragments RPC. Wait(sec): 5, host: xxx(ip)

批量插入数据时遇到的超时错误,默认超时时间为300s,可以登录到mysql客户端用命令自定义修改query_timeout参数,我这里改成了3000s就不报错了。

SHOW VARIABLES LIKE “%query_timeout%”;
SET query_timeout = 3000;

Caused by: java.sql.SQLException: timeout when waiting for send fragments RPC. Wait(sec): 5, host: 192.168.1.246

        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)

        at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)

        at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)

        at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1098)

        at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1046)

        at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1371)

        at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:1031)

        at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)

        at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java)

        at org.apache.dolphinscheduler.plugin.task.sql.SqlTask.executeUpdate(SqlTask.java:312)

        at org.apache.dolphinscheduler.plugin.task.sql.SqlTask.executeFuncAndSql(SqlTask.java:210)

        at org.apache.dolphinscheduler.plugin.task.sql.SqlTask.handle(SqlTask.java:161)

        ... 9 common frames omitted

10、增加字段

ALTER TABLE dwd_contractlist_area  ADD COLUMN menuarea_origin_gate2 int(11) NULL COMMENT '级别' AFTER hl_gettime;
ALTER TABLE dwd_contractlist_area  ADD COLUMN menuarea_origin_menuname  text NULL COMMENT 'menuarea表 区域名称' AFTER menuarea_origin_gate2;
ALTER TABLE dwd_contractlist_area  ADD COLUMN menuarea_big_area int(11) NULL COMMENT  '大区id' AFTER menuarea_origin_menuname;
ALTER TABLE dwd_contractlist_area  ADD COLUMN menuarea_big_area_menuname text NULL COMMENT '大区名称' AFTER menuarea_big_area;

 

11、Doris修改表的评论

alter table dws_potential_customer_amount_base modify comment '当前12个月内的客户名称及月份差分析表';

12、删除分区

PARTITION p0 VALUES [('0000-01-01'), ('2023-07-28')) 

alter table dws_cost_money_year drop partition p0;
alter table dws_rmf_customer_value_analysis drop partition p0;

ALTER TABLE dws_cost_money_year SET ("dynamic_partition.enable" = "false")

alter table dws_cost_money_year drop partition P20230731;

alter table dws_rmf_customer_value_analysis drop partition P20230731;

alter table ads_cost_money_month drop partition p0;

alter table ads_rmf_customer_value_analysis drop partition p0;

alter table ads_cost_money_year drop partition p0;

alter table ads_sale_money_company_analyse_month drop partition p0;

13、分区、分桶

doris 也有分区和分桶,但和hive不同

Table是有很多分区Partition的

Partition 可以视为是逻辑上最小的管理单元。数据的导入与删除,都可以或仅能针对一个 Partition 进行(不能直接操作Tablet)。

一个 Partition包含多个数据分片Tablet组成,Tablet 包含若干数据行

所以本质上Tablet 是数据移动、复制等操作的最小物理存储单元,一定要设置分桶,可以不设置分区

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值